CODEWITHSUNDEEP
mysqlsql
SuperFrog
SuperFrog

Reputation: 7674

query to fetch records and their rank in the DB

I have a table that holds usernames and results. When a user insert his results to the DB, I want to execute a query that will return the top X results ( with their rank in the db) and will also get that user result and his rank in the DB. the result should be like this:

1 playername 4500

2 otherplayer 4100

3 anotherone 3900 ...

134 current player 140

I have tried a query with union, but then I didnt get the current player rank. ideas anyone?

The DB is MYSQL.

10x alot and have agreat weekend :)

EDIT

This is what I have tried:

(select substr(first_name,1,10) as first_name, result FROM top_scores ts WHERE result_date >= NOW() - INTERVAL 1 DAY LIMIT 10) union (select substr(first_name,1,10) as first_name, result FROM top_scores ts where first_name='XXX' and result=3030);

Upvotes: 1

Views: 225

Answers (4)

Witold Sosnowski
Witold Sosnowski

Reputation: 506

I assume you have PRIMARY KEY on this table. If you don't, just create one. My table structure (because you didn't supply your own) is like this:

id          INTEGER
result      INTEGER
first_name  VARCHAR

SQL query should be like that:

SELECT @i := @i+1 AS position, first_name, result FROM top_scores, (SELECT @i := 0) t ORDER BY result DESC LIMIT 10 UNION
SELECT (SELECT COUNT(id) FROM top_scores t2 WHERE t2.result > t1.result AND t2.id > t1.id) AS position, first_name, result FROM top_scores t1 WHERE id = LAST_INSERT_ID();

I added additional condition into subquery ("AND t2.id > t1.id") to prevent multiple people with same result having same position.

EDIT: If you have some login system, it would be better to save userid with result and get current user result using it.

Upvotes: 0

John
John

Reputation: 763

Based on what I am reading here:

Your table structure is:

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(50) | YES  |     | NULL    |       |
| result | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

Table Data looks like:

+---------+--------+
| name    | result |
+---------+--------+
| Player1 |   4500 |
| Player2 |   4100 |
| Player3 |   3900 |
| Player4 |   3800 |
| Player5 |   3700 |
| Player6 |   3600 |
| Player7 |   3500 |
| Player8 |   3400 |
+---------+--------+

You want a result set to look like this:

+------+---------+--------+
| rank | name    | result |
+------+---------+--------+
|    1 | Player1 |   4500 |
|    2 | Player2 |   4100 |
|    3 | Player3 |   3900 |
|    4 | Player4 |   3800 |
|    5 | Player5 |   3700 |
|    6 | Player6 |   3600 |
|    7 | Player7 |   3500 |
|    8 | Player8 |   3400 |
+------+---------+--------+

SQL:

 set @rank = 0;
 select 
top_scores.* 
 from 
 (select ranks.* from (select @rank:=@rank+1 AS rank, name, result from ranks) ranks) top_scores
where 
top_scores.rank <= 5
or (top_scores.result = 3400 and name ='Player8');

That will do what you want it to do

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562871

SET X = 0;

SELECT @X:=@X+1 AS rank, username, result
FROM myTable
ORDER BY result DESC
LIMIT 10;

Re your comment:

How about this:

SET X = 0;

SELECT ranked.* 
FROM (
    SELECT @X:=@X+1 AS rank, username, result
    FROM myTable
    ORDER BY result DESC
) AS ranked
WHERE ranked.rank <= 10 OR username = 'current';

Upvotes: 1

anil.vemulapalli
anil.vemulapalli

Reputation: 21

assuming your table has the following columns:

playername 
score
calculated_rank

your query should look something like:

select calculated_rank,playername, score 
from tablename 
order by calculated_rank limit 5

Upvotes: 0

Related Questions