Query help - select user's best x scores

[vendejp]

Hello all,
Im trying to get something like each user's best x scores from 1 table. x will change.

I have a solution that is not fully functional.

See table and query below. As an example, Id like each user's best 2 scores.

With the table and data below, I should get a total of 6 records:
[code]
---RESULTSET ---
| USER | POINTS |
|  1  |  3  |
|  1  |  4  |
|  2  |  10  |
|  2  |  10  |
|  3  |  9  |
|  3  |  6  |
[/code]

The problem with my solution is that user 3 would actually return 3 records since there are 2 scores that are the same that are second highest. "limit" in subselect doesnt work for my version (5.0.24) of MySQL.
[i]
Adding DISTINCT removes valid values (such as multiple "10" values for user 2 since those are his/her highest)
[/i]
Simple problem with difficult solution, or am I missing a better technique? I appreciate any help. To aid my shotty explanation, see sql below.

[b]
Thanks!
[/b]
[code]
---RESULTSET ---
| TABLE TEST |
|  1  |  1  |
|  1  |  2  |
|  1  |  3  |
|  1  |  4  |
|  2  |  10  |
|  2  |  10  |
|  2  |  5  |
|  2  |  3  |
|  2  |  0  |
|  3  |  9  |
|  3  |  6  |
|  3  |  6  |
|  3  |  0  |
[/code]


CREATE TABLE `test` (
`user` int(11) default NULL,
`points` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

insert into test values( 1,1),(1,2),(1,3),(1,4),(2,10),(2,10),(2,5),(2,3),(2,0),(3,9), (3,6),(3,6),(3,0)

select user,points from test
where ( select count(*)
from test t where t.user = test.user and t.points > test.points) < x
order by user , points desc ;

[bibby]

How about..?

[code]
select
  user,
  points,
  count(*) number_of_times
from test
where 1
and user = 3
group by
  points
order by points desc
limit 3 ;
  #(or limit x)
[/code]

[vendejp]

thanks bibby, but this isnt quite it.

First, Id like to do this in one statement, rather than having to loop through each user and execute it per user (i.e. if there are 100 users).

If i did have to loop though, id settle for it for now.  Unfortunately, id like to use this query in an update, so it would be a subselect and LIMIT isnt allowed in subselects, so Id have to put all the ids into an array and implode it into something like:

[i]update users set flag=1 where primary_key in (1,2,3,4...);[/i]
rather than the following (which wont work because of the nested LIMIT)
[i]update users, set flag=1 where user in(select primary_key from test where user = 2 order by points desc limit 2);[/i]


However, if I execute the query for user 2, rather than getting back the best 2 scores (10 points 2x) I get
[code]
| USER | POINTS | NUMBER OF TIMES
| 2    |  10  |  2
| 2    |  5    |  1
[/code]

I appreciate your help though!!

[printf]

I would do it like this, if you don't have the limit...

[code]SELECT user, SUBSTRING_INDEX(GROUP_CONCAT(points ORDER BY points DESC SEPARATOR ','), ',', 2) AS points FROM test GROUP BY user ORDER BY user;[/code]

It would output something like this...

[code] user    points
1 4,3
2 10,10
3 9,6[/code]

Just change where the [b]2[/b] is in the query, with how many [b]points[/b] results you want to return


printf