Removing duplicates from SQL results

Question

I'm having trouble trying to retrieve data from mySQL table. The table holds all the lap times from a racing event by all the participants. The table has multiple entries for each race. I would like to select each participant's best lap time, however I'm not sure how to get rid each participant's duplicates.

I've tried the following statement.

SELECT *
FROM   laptimes
GROUP  BY laptime,
          username

The table looks like this:

lap username    laptime
2   Blackvault  1200.000    
1   Blackvault  1234.000    
3   Elmo        1235.000

I'd like to display only lap 2 of Blackvault and Elmo's only lap. The SQL will need to be generic as there could be N number of racers and N number laps.

Any help is appreciated.

[SQL Group By with an Order By][1] [1]: http://stackoverflow.com/questions/27983/sql-group-by-with-an-order-by This might be helpful. — F4b, May 24 '15 at 17:42

Gordon Linoff · Accepted Answer · 2015-05-24T17:47:44.407

2

If you want to get the original data row (in MySQL), then you need to get the best time and connect it to the original data. Here is a method using join:

select lt.*
from laptimes lt join
     (select username, min(laptime) as laptime
      from laptimes
      group by username
     ) lu
     on lu.username = lt.nusername and lu.laptime = lt.laptime;

If you don't need the id (or other columns), then the subquery might be sufficient for your purposes.

edited May 24 '15 at 17:47

answered May 24 '15 at 17:43

Gordon Linoff

1,242,037
58
646
786

Best laptime means `min(laptime)` – Hotdin Gurning May 24 '15 at 17:46
Thanks for the fast response. That seems to return Blackvault's slowest time rather than his fastest. Does an order by desc cause need to be added? – Blackvault May 24 '15 at 17:46
@Blackvault . . . No, `max()` just needs to be replaced with `min()`. – Gordon Linoff May 24 '15 at 17:48

Hotdin Gurning · Answer 2 · 2015-05-24T17:50:44.977

1

If you need to select only best laptime then you need this :

SELECT lap,username, min(laptime) as laptime
FROM laptimes 
GROUP BY username

edited May 24 '15 at 17:50

answered May 24 '15 at 17:38

Hotdin Gurning

1,821
3
15
24

i think you want to remove laptime from group by. – Tim3880 May 24 '15 at 17:49
@Tim3880 : Ah yes, you right. Thanks for correction. – Hotdin Gurning May 24 '15 at 17:50

Removing duplicates from SQL results

2 Answers2