Need helping in writing query

[sizzlingbrains]

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...

[sizzlingbrains]

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

[Lionel C]

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...