Let's say I have a table with two ids, series_id and match_id. The series_id can be repeated, but match_ids are unique. In addition, the number of possible match_ids per series can vary from 1 to 5. I want to select the top 10 series and all the match_ids associated with them. In other words, if I want to select the top 10 series, I could end up selecting as few as 10 match_ids or as many as 50 match_ids. How can I do that?
Edit: Here's an example list of the top 10 match ids.
MatchID SeriesID
250059 3155
260071 3156
260086 3163
270079 3157
270080 3157
270081 3157
340566 3123
340607 3124
350551 3123
350552 3123
As you can see, SeriesId repeats three times. for 3157, and at least two times for 3123.
Ideally, I'd want the following:
MatchID SeriesID
1001400020 3150
1001400019 3150
1001400010 3150
270081 3157
270080 3157
270079 3157
1001320026 3151
1001320025 3151
1001320024 3151
1001320023 3151
1001320017 3151
750596 3146
750595 3146
750592 3146
750591 3146
760569 3145
760568 3145
760567 3145
760566 3145
760565 3145
760517 3144
760515 3144
760512 3144
1001380039 3131
1001380038 3131
1001380037 3131
1001380034 3131
1001310030 3128
1001310029 3128
1001300036 3128
1001370029 3130
1001370028 3130
1001370027 3130
1001370026 3130
1001370020 3130
260086 3163
Because in this case, the number of unique series ids is 10 in total.
This works in MySQL:
SELECT *
FROM myTable m INNER JOIN
(SELECT DISTINCT series_id
FROM myTable
ORDER BY series_id
LIMIT 10) t ON t.series_id = m.series_id