MYSQL :: Ranking Scores

Calculating AVG Scores
I use the following query to determine the average score by teams taking a particular test.

SELECT teams.teamname, teams.id, AVG(game_sessions.score) as score
FROM teams, game_sessions
WHERE teams.id = game_sessions.team_id
GROUP BY teams.teamname
ORDER BY score DESC LIMIT 0,25

What I'd like to change now is instead of averaging all of the scores from each team I'd like to only average the top 50% of scores from each team.

Select Avg Of Best 2 Scores
is there a way in MySQL 4 to select the average of only the best two scores grouped by player from a table storing game details? DO I need a transaction for this?

Golf Scores
Say I have a table like this:

table DATA:
name | varchar | PRIMARY KEY
score| int

and I want to know what name is in second place. When I do a

Code:

SELECT name FROM data ORDER BY score ASC LIMIT 2,1;

mysql returns the name of the person in 2nd. But say I have the name and I want to know what place they're in. How could I do that?

Update Scores
I have problem with getting the info I want from my table (HighscoreList) with three columns (Results, Name, Date) where Name is primary and cannot contain duplicates.
When I add a post with an existing name and if it has a better score want to update its score. How do I do that?
I've tried a lot of suggestions I've found in books and from the net but I think I've gone blind for the obvious solutions here so I hope someone can help me.
If I use REPLACE like this:
REPLACE INTO HighscoreList (Results, Name, Date) VALUES ($results, $name, $date)
how do REPLACE (or UPDATE) know that it should find the same Name and change its Result and Date instead of finding the same Result and change the name and Date

Return Cumalative Scores
I have 2 tables.

`match` (match_id, match_date, home_team, away_team, home_score, away_score)
`predictions` (prediction_id, match_id, user_id, home_score, away_score)

I have a query that can return the number of points for a user between 2 dates.

I want a query to return the number of points a user has received each week (cumalative) between 2 dates. I am really struggling.

A Ranking Leaderboard
I am trying to design and implement an online leaderboard in MySQL and PHP.  Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved.

Correct me if I'm wrong but data is not stored in any order as such in a SQL db.  It is the query you write/code which does the sorting.  How then is it possible to have a leaderboard with rankings on?

For example if I had a basic table with fields of name and points.

You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc.  However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical.  You could obviously pull an individuals record up by using WHERE and the name.

How would I get SQL to output the users ranking?  Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated...

Surely this kind of thing has been done before.  I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.

Ranking Results
Im trying to display baseball team stats, along with the ranking for that particular league.
for example, there are 5 teams in the league, Team A has 12 HR, B has 15 HR, C has 3 HR, D has 3, and E has 20.
I want to show on each individual team page their stat, with the rtanking for the league.

Ranking MySQL
I have a query that extracts the last 7 records from a database based on its criteria and have it ordered by its date. What i want to do is add a column in the select statement and put in the numbers from 1 to 7 into the column in this added column.

I'm using the Space(1) as Number function but i am wondering how do I put 1 to 7 in the table. Its pretty much like a ranking system.

Ranking Users
i'm trying to rank the users in our site.
table contains:
- userid
- userrates
and with thousands of records.
member with the highest rate goes #1, next is #2 and so on.
i want the query to be like:
Code:
<something goes here> WHERE userid = '$userid' order by userrates desc
from the code above it will show the rank of this user.
then the next user logsin and it shows again his/her ranking.
hope someone understands this
any input is appreaciated

Ranking System
I've made a forum and i want to have a ranking system with it. I managed to display a postcount but i also want a few images to be displayed, like if you have made more then 50 posts, it displays 50.jpg or something, how can i accomplish this, in another way then just str_replace()ing all the number of posts?
And how do i display 2, like if you have 50 posts, 50.jpg and if you have 60 posts, 60.jpg.

Ranking Leaderboard In MySQL!?
I am trying to design and implement an online leaderboard in MySQL and PHP. Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved.

Correct me if I'm wrong but data is not stored in any order as such in a SQL db. It is the query you write/code which does the sorting. How then is it possible to have a leaderboard with rankings on?

For example if I had a basic table with fields of name and points.

You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc. However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical. You could obviously pull an individuals record up by using WHERE and the name.

How would I get SQL to output the users ranking? Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated...

Surely this kind of thing has been done before. I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.

Ranking Query (self Join)
I would like to get a rank (row # of ordered results) of an article based on its average rating in relation to all other articles. I have a ratings table that holds all of the individual votes cast by users (ratingID, articleID, userID, rating). So I need to order the articles by AVG(ratings.rating) and then come up with an individual article's row # to find its ranking.

I know this involves joining the ratings table on itself with a greater than or less than operator, but i'm completely lost when it comes to self-joins. I know this is easy, but there is a small trick to it and i'm clueless.

Ranking For Multiple Columns
A query in my application return several columns with amounts, Now i need to add a ranking to all those columns seperatly.

So the result should be something like this:

ID     Amount1  Ranking1  Amount2 Ranking2
----   -------- --------- ------- --------
135     123.34      4       23.00     3
184     160.23      1       60.89     2
845     140.22      3      100.20     1
987     155.00      2        1.20     4

The ID isnt important here.

The only solution i can think of now is to create a (temp) table, populate it with the query, and then sort it for each ranking and fill the ranking one by one.

Is there a way to get the ranking filled in  the same pass as the amounts are calculated?

Sorting And Put Ranking In Field
I have 3 field in my table Ranking, Name and Score. Is it possible to sort all record to order by Score and put Ranking no. to each record by using query not much?

Scoreboard Ranking System
I have created a high score table that will potentially store > 10000 user scores, and need to determine ranking for items pulled out. Ther have been posts detailing how to do this when selecting ALL records, but in my case, I need to pull out specific rows, and determine their rank against the entire table.
For instance, a user gets a high score that would rank them at #1200, I need to pull out their score, as well as the 3 above and 3 below. I accomplished this by storing the insert_id, and selecting rows where the score is above and below (in seperate queries), but this makes it quite impossible to get a rank.
An alternative method is having a rank column and updating it every time a new score is added, but if it cant be done in one query, it would be a very inefficient method.
Does anyone have any solutions on how to approach this? I am using mysql 3.23, so I dont have use of sub-queries.

Ranking System: Retrieving Results Around Your Rank
I'm building a ranking system for a game. I'm trying to determine the best way to select a players rank, and then display the records around that players ranking.

For instance:

Username | Rank
steve | 6000
bob | 5000
jane | 4000
chris | 3000
brian | 2000
tim | 1000
molly | 0

Assuming chris is the current player, I want to select his rank, and then run a query that returns the two players ranked higher than him, and the two players ranked lower than him. So the query would return the following:

bob | 5000
jane | 4000
chris | 3000 //I'd rather leave his own rank out.. but it's not bad like this.
brian | 2000
tim | 1000

What I can't figure out how to do, is determine the row that is specific to Chris's rank. I would need to define a starting point (-2 rows from Chris), and an end point (+2 rows from Chris), order by and limit the results... what I can't figure out though, is how to get the specific row number to start from.

For instance, lets say Chris is row 583 of my database... how do I retrieve that number?

Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14
or 3.23 SQL to get student grade ranking where tied grade have the same
rank.

I used to set it through MS Access 2002 and use this kind of query:

SELECT nilai.studentNIS, nilai.studenttestmark,
(SELECT COUNT(*) FROM tblStudentGrades
WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM
tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC;

I've been looking around mySQL documentation and read that subquery can
be redefined as INNER JOIN or using two SQL statement via variable? I
have no idea on the basics of how to set it out though.

Could one of you please help give a me a sample on how this kind of
query should be done on mySQL? Is it possible to do it in single line?
And without having to use PHP/Perl scripts?

Or maybe I should have approach it differently?

Full-Text Search: Truncation Operator And Relevance Ranking?
We would like to begin supporting the truncation operator on our website's search engine--however, we still require the results to be sorted by relevance. If a client enters "televis*" into the search engine, and we perform the search in boolean mode, pages containing "television", "televise", "televised", etc will be returned. However, because it is a boolean mode search, all rows will have a relevancy value of 1.

A user-provided "workaround" on the MySQL site said to use the same keywords but NOT in boolean mode to get a usable relevance ranking. However, a standard fulltext search for "televis" or "televis*" will return no rows.

Ranking Scores

I need to create a query that will sort a bunch of scores in ascending order and then add a column with their rank code from 1 to however many is in the table. the link below shows an example image of what i need to happen.

I need to create a query that will sort a bunch of scores in ascending order and then add a column with their rank code from 1 to however many is in the table.
the link below shows an example image of what i need to happen.