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

Answer 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`

Answer 2

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