Select top score only from MySQL database

Question

I have a MySQL statement that pulls back the top 15 scores from a table of users.

select @rownum:=@rownum+1 'rank', driverName, teamColour, totalScore, totalTime, didTyreChange from entries p, (SELECT @rownum:=0) r WHERE totalTime > 1000 and progress = 19 order by totalScore desc, totalTime asc limit 15

The conditions are, that the player must have completed all the questions (where progress = 19) and the final time must be more than 1 second (totalTime > 1000). The statement also returns their rank as a variable (@rownum:=@rownum+1 'rank').

The result of this query is the following

MySQL Results

What I now want to do, is extend the results to only show a players top score. I have had a look at DISTINCT and GROUP BY but get these results with the following GROUP BY statement

select @rownum:=@rownum+1 'rank', driverName, teamColour, totalScore, totalTime, didTyreChange from entries p, (SELECT @rownum:=0) r WHERE totalTime > 1000 and progress = 19 group by driverName order by totalScore desc, totalTime asc limit 15

Group by results

and nowhere with DISTINCT as I get error code 1064. I have tried both

select @rownum:=@rownum+1 'rank', DISTINCT(driverName), teamColour, totalScore, totalTime, didTyreChange from entries p, (SELECT @rownum:=0) r WHERE totalTime > 1000 and progress = 19 order by totalScore desc, totalTime asc limit 15

and

select DISTINCT driverName, @rownum:=@rownum+1 'rank', teamColour, totalScore, totalTime, didTyreChange from entries p, (SELECT @rownum:=0) r WHERE totalTime > 1000 and progress = 19 order by totalScore desc, totalTime asc limit 15

Neither provides the desired results. I'm wondering if there is an easy way to achieve this within the statement, or to do it on the PHP side of things instead.

The desired resultL

viper         1  9810
Maverick      2 25420
Racer roasty  3 28850
.. .
...          15

In other words, no user should appear in the results twice, only the quickest totalTime is displayed for each user.

Slimmed down SQLFiddle here http://sqlfiddle.com/#!2/36d3dc/7 (8000 char limit reached).

Brother your explanation is still confusing. please explain more and in detail — Ronak Shah, Aug 21 '14 at 12:33
http://stackoverflow.com/questions/15410336/how-to-select-top-n-rows-from-each-group-id-in-same-table?lq=1 — sridesmet, Aug 21 '14 at 12:37
Added group by query and a bit more detail on the desired result — terrorfall, Aug 21 '14 at 12:42
There is no viper in the data set. In consequence, this result set will be hard to achieve. — Strawberry, Aug 21 '14 at 16:44

score 2 · Accepted Answer · edited Aug 26 '14 at 13:33

2

SELECT @i:=@i+1 rank, a.*
  FROM 
     ( SELECT x.*
         FROM entries x
         JOIN (SELECT drivername, MIN(totaltime) min_totaltime FROM entries WHERE progress = 19 AND totaltime > 1000 GROUP BY drivername) y
           ON y.drivername = x.drivername
          AND y.min_totaltime = x.totaltime
     ) a
     , (SELECT @i:=0) i
 ORDER 
    BY totalScore desc, totalTime asc;

http://sqlfiddle.com/#!2/36d3dc/17

edited Aug 26 '14 at 13:33

terrorfall

1,121
3
16
33

answered Aug 26 '14 at 09:17

Strawberry

33,750
13
40
57

That is applying the rank to the recordset before ordering, therefore the rank is actually incorrect. When this is applied to the full data set, viper comes out at rank #139. – terrorfall Aug 26 '14 at 12:21

Ronak Shah · Answer 2 · 2014-08-22T10:04:45.497

0

If I understood your problem correctly then below query will help you:

select *,@rownum:=@rownum+1 as 'rank' from (select * from (select  driverName, teamColour, totalScore, totalTime, didTyreChange from entries p WHERE totalTime > 1000 and progress = 19 order by totalScore desc, totalTime asc  )a group by driverName)c , (select @rownum:=0)f

edited Aug 22 '14 at 10:04

answered Aug 21 '14 at 12:45

Ronak Shah

1,539
2
13
20

1

@PeteSimmons. If you're going to use the evil star (and you shouldn't), it has to go first. – Strawberry Aug 21 '14 at 14:46
@Strawberry yeah, want to avoid that like the plague! As I have in my original statement – terrorfall Aug 21 '14 at 16:25
@pete I have update my query now try.. It will be great if you can load your data in sqlfiddle then I can test my query before posting here – Ronak Shah Aug 22 '14 at 10:05

score 0 · Answer 3 · answered Aug 26 '14 at 12:32

This should do:

select @rownum:=@rownum+1 'rank', driverName, totalScore, MIN(totalTime) 
from entries p, (SELECT @rownum:=0) r 
WHERE totalTime > 1000 and progress = 19 
group by driverName 
order by totalScore desc, totalTime asc

http://sqlfiddle.com/#!2/36d3dc/20/0

Select top score only from MySQL database

3 Answers3