mysql - 列出具有唯一列的结果

Question

我只想列出唯一持有者的前 6 名比赛记录。我的意思是，持有者进入列表不应与他的另一项记录一起列出。我目前使用下面的查询来列出前 6 次。

mysql> select * from racerecords order by record_time asc, date asc;
+----+---------+------------+-------------+---------------------+----------+
| id | race_id | holder     | record_time | date                | position |
+----+---------+------------+-------------+---------------------+----------+
|  2 |      10 | Stav       |          15 | 2014-08-11 19:43:49 |        1 |
|  1 |      10 | Jennifer   |          15 | 2014-08-13 19:43:19 |        1 |
|  4 |      10 | Jennifer   |          16 | 2014-08-02 19:44:27 |        1 |
|  5 |      10 | Osman      |          17 | 2014-08-04 19:44:57 |        1 |
|  7 |      10 | Gokhan     |          18 | 2014-08-15 19:45:37 |        1 |
|  3 |      10 | MotherLode |          25 | 2014-08-01 19:44:11 |        1 |
+----+---------+------------+-------------+---------------------+----------+
6 rows in set (0.00 sec)

如您所见，持有人“Jennifer”被列出了两次。我希望 mySQL 在她进入列表后跳过她。我想要生成的结果是:

+----+---------+------------+-------------+---------------------+----------+
| id | race_id | holder     | record_time | date                | position |
+----+---------+------------+-------------+---------------------+----------+
|  2 |      10 | Stav       |          15 | 2014-08-11 19:43:49 |        1 |
|  1 |      10 | Jennifer   |          15 | 2014-08-13 19:43:19 |        1 |
|  5 |      10 | Osman      |          17 | 2014-08-04 19:44:57 |        1 |
|  7 |      10 | Gokhan     |          18 | 2014-08-15 19:45:37 |        1 |
|  3 |      10 | MotherLode |          25 | 2014-08-01 19:44:11 |        1 |
+----+---------+------------+-------------+---------------------+----------+

我什么都试过了。 GROUP BY holder 生成错误的结果。它获得了保持者的第一个记录，尽管不是最好的。在此表中，它会生成如上所示的输出，因为 id:1 是我为 Jennifer 插入的第一条记录。

如何生成输出结果如上？

Answer 1

期望的结果可以通过这个查询来实现，但是它是性能密集型的。我在 SQLFiddle http://sqlfiddle.com/#!2/f8ee7/3 中重现了结果

select * from racerecords
where 
(HOLDER, RECORD_TIME) in (
 select HOLDER,min(RECORD_TIME) from racerecords
group by HOLDER)

您似乎没有在子查询中包含 Where 子句。试试这个

select * from racerecords
where 
(HOLDER, RECORD_TIME) in (
 select HOLDER,min(RECORD_TIME) from racerecords where race_id =17
group by HOLDER )
And race_id =17
Order by RECORD_TIME