I have a database of event results. There is a table called races and a table called entrants.
I would like to display the top ten entrants (noting the example below is truncated and won't reach 10) for each entrant's surname letter of the alphabet, sorted by the number of races each entrant has participated in.
The entrant table structure is like follows:
Name ¦ Surname ¦ Letter ¦ ID ¦ Nationality
John ARBOR ¦ ARBOR ¦ A ¦ 1 ¦ gb
István NAGY ¦ NAGY ¦ N ¦ 2 ¦ hu
Günter HOLZMANN ¦ HOLZMANN ¦ H ¦ 3 ¦ at
Bernard NEWMAN ¦ NEWMAN ¦ N ¦ 4 ¦ gb
Feliciano DE ALMEIDA ¦ DE ALMEIDA ¦ D ¦ 5 ¦ it
Joe BIDEN ¦ BIDEN ¦ B ¦ 6 ¦ us
Nina ASIM ¦ ASIM ¦ A ¦ 7 ¦ fr
Zac NECCHI ¦ NECCHI ¦ N ¦ 8 ¦ au
The races table structure example is as follows:
race_id ¦ Pos ¦ Name ¦ Time
0001 ¦ 1 ¦ Bernard NEWMAN ¦ 4:56:34.386
0001 ¦ 2 ¦ Günter HOLZMANN ¦ 4:59:59:001
0001 ¦ 3 ¦ Nina ASIM ¦ 5:02:14.443
0001 ¦ 4 ¦ Zac NECCHI ¦ 5:05:10.101
0002 ¦ 1 ¦ István NAGY ¦ 0:34:05.714
0002 ¦ 2 ¦ Joe BIDEN ¦ 0:35:00:559
0002 ¦ 3 ¦ Bernard NEWMAN ¦ 0:35:10.050
0002 ¦ 4 ¦ Günter HOLZMANN ¦ 0:36:09.572
0003 ¦ 1 ¦ Feliciano DE ALMEIDA ¦ 1:07:44.678
0003 ¦ 2 ¦ John ARBOR ¦ 1:10:13.873
0003 ¦ 3 ¦ Bernard NEWMAN ¦ 1:11:44.665
0003 ¦ 4 ¦ Nina ASIM ¦ 1:11:44.788
0003 ¦ 5 ¦ István NAGY ¦ 2:12:12.650
The intended SQL result for the letter 'A' (obviously the real dataset would be limited to 10 not 2):
Letter ¦ Name ¦ Entries
A ¦ Nina ASIM ¦ 2
A ¦ John ARBOR ¦ 1
The intended SQL result for the letter 'N' (obviously the real dataset would be limited to 10 not 3):
Letter ¦ Name ¦ Entries
N ¦ Bernard NEWMAN ¦ 3
N ¦ István NAGY ¦ 2
N ¦ Zac NECCHI ¦ 1
... and so on...
I'm building an index page and intend to display ten names for each letter of the alphabet, showing the ten most prolific names for each letter sorted by their number of race entries.
Started with the basic:
SELECT e.name from entrants e WHERE e.letter = "A" LIMIT 10
Do I need a subquery?
This query produces a result without the limit of ten per letter:
SELECT r.entrant, r.entrant_id, e.letter, COUNT(*) AS num_races
FROM races r
LEFT JOIN entrant e ON r.entrant_id = e.id
GROUP BY 1
ORDER BY e.letter ASC, num_races DESC
