-
Need helping in writing query
Hi friends,
I have a table with cols ( gameid , score , userid ) where gameid is a value from {1-10} represting a unique game . I store only top 10 scores for any game to see the top 10 scorers.
An user when played a game ( with some gameid ) and submits score , i check if the score has place in top 10 records for that game and accordignly update the table.
Now I have to write a query for this scenario.
Given a userid, return all the gameids if his score is in top 3 scores for a gameid
eg output:
gameid position
1 3
6 1
9 2
( user is not in top3 for rest of the games. )
Please reply asap...
-
Repeating if i am not clear
For eg, select top 3 scorers from each class where table structure is { CLASSID, SNAME, SCORE }
CLASSID SNAME SCORE
1 x 200
1 y 400
1 a 250
1 b 300
1 c 270
2 m 400
2 n 310
2 o 285
2 a 450
2 b 199
Result:
1 400 y
1 300 b
1 270 c
2 450 a
2 400 m
2 310 n
-
Hi,
I am not an expert in MySQL, I would do an union of all the ordered by descending score and limited to 3 elements
SELECT * FROM yrDB WHERE CLASSID = ( SELECT * FROM yrDB WHERE CLASSID = 1 ORDERED by SCORE DESC) LIMIT 3
UNION
SELECT * FROM yrDB WHERE CLASSID = ( SELECT * FROM yrDB WHERE CLASSID = 2 ORDERED by SCORE DESC) LIMIT 3
________________________
More DB ? visit http://www.raima.com/
UNION...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|