MySQL problem: How to get desired rows

Question

I have been trying to solve this problem for 2 hours now but I cant understand the solutions others have given people with a similar problem. Ive seen some answers but can't apply it to my own needs.

I have a table of users and their times in different sports events. I need to make a scoretable that shows the user with the best time, second best etc. The table before sorting and retrieving looks as follows:

|     Name     |     Time     |     Date     |
''''''''''''''''''''''''''''''''''''''''''''''
|    Jack      |   03:07:13   |  2010-12-01  |
|    Peter     |   05:03:12   |  2010-12-03  |
|    Jack      |   03:53:19   |  2010-12-04  |
|    Simon     |   03:22:59   |  2010-12-02  |
|    Simon     |   04:01:11   |  2010-12-09  |
|    Peter     |   03:19:17   |  2010-12-06  |
''''''''''''''''''''''''''''''''''''''''''''''

|     Name    |    Time    |    Date     |
''''''''''''''''''''''''''''''''''''''''''
|    Jack     |   03:07:13 | 2010-12-01  |
|    Peter    |   03:19:17 | 2010-12-06  |
|   Simon     |   03:22:59 | 2010-12-02  |
''''''''''''''''''''''''''''''''''''''''''

I know answers to this problem lie in another question asked on this very site: CLICK HERE

I just have no idea how to apply it to fullfill my needs.

Help is highly appreciated. Thank you

-Joonas

This is a pretty bad fundamental database design to start from. Your Users should really be in a separate table to their Times. — fearofawhackplanet, Jun 12 '10 at 20:30
@fearofawhackplanet: You are right, but maybe the first one is a view. — True Soft, Jun 12 '10 at 20:52

Shaun · Answer 1 · 2010-06-12T21:01:21.090

1

Replace table_name with the name of the table:

SELECT DISTINCT `name`, `time`, `date` 
FROM `table_name`
INNER JOIN
(
    SELECT `name`, MIN(`time`) as MinTime
    FROM `table_name`
    GROUP BY `name`
)
groupedtime ON `table_name`.`name` = groupedtime.`name` AND `table_name`.`time` = groupedtime.MinTime
 ORDER BY `time`

edited Jun 12 '10 at 21:01

answered Jun 12 '10 at 20:30

Shaun

4,789
3
22
27

1

You can add `ORDER BY time` to order the results by time, like in the example. – True Soft Jun 12 '10 at 20:48
@True Soft: might as well be consistent with the back-ticks (`) – Sev Jun 12 '10 at 20:57
@Sev: Yes, but OP shouldn't name his fields like that in the first place. – True Soft Jun 12 '10 at 21:03

score 0 · Answer 2 · answered Jun 24 '14 at 17:21

0

SELECT Name, min(Time) as BestTime
FROM test
GROUP BY Name
ORDER BY BestTime

Let me know if it doesn't work!

answered Jun 24 '14 at 17:21

Sanmeet

56
4

score -1 · Answer 3 · answered Jun 12 '10 at 20:40

-1

select name, min(time)
from mytable
group by name
order by time desc

answered Jun 12 '10 at 20:40

Randy

16,480
1
37
55

The goal is to get the best time for each person. This gives you a sorted list of all times for all people and simply places their times together in groups. – Shaun Jun 12 '10 at 20:51
Shaun - you are incorrect. This gives a sorted list from best time to worst, with each persons best (shortest) time. – Randy Jun 13 '10 at 13:29
Trus Soft - you are incorrect. the second column returns the best time for each person – Randy Jun 13 '10 at 13:31

MySQL problem: How to get desired rows

3 Answers3