MySQL: return first row from join

Question

I have 2 tables "quizzes" & "users", I need to return list of first user took each quiz, with quiz_id:

tables structure

"quizzes" structure:
id     name
1     England
2     france
3     Japan
4     USA
5     UAE
6     Sweden
7     Italy
8     Brazil
9     South Korea
10    India


"users" structure:
id    user_id     quiz_id
1       1            1
2       1            2
3       2            1
4       3            4
5       1            4
6       5            9
7       2            9
8       3            8
9       3            9
10      3            7

I need to run query to return first "user_id" took each "quiz", (order by users.id ASC)

expected results:
quiz_id     user_id
   1           1
   2           1
   4           3
   7           3
   8           3
   9           5

thanks,

This is a classic [greatest-n-per-group](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) (or least, in this case) problem, of which there are many existing solutions. — Clockwork-Muse, Jun 14 '15 at 13:28

score 1 · Accepted Answer · answered Jun 14 '15 at 12:36

1

You first group by quiz and pick minimal id and then select based on those ids:

select quiz_id, user_id
from users 
where id in(select min(id) from users group by quiz_id)

answered Jun 14 '15 at 12:36

Giorgi Nakeuri

35,155
8
47
75

thx, but is there way to select them based on "date" ASC, rather than "min(id)" ? – mwafi Jun 14 '15 at 12:38
Sure you can do this. – Giorgi Nakeuri Jun 14 '15 at 12:41
@mwafi . . . Your question here has no mention of a date column. If you have another question, ask it as another question, not in a comment. – Gordon Linoff Jun 14 '15 at 13:01

score 0 · Answer 2 · answered Jun 14 '15 at 12:42

0

Select quiz_id, user_id
  from users
    Where  (quiz_id, id) in
       (
       Select quiz_id, min(id) id
         From users
           Group by quiz_id
       )

answered Jun 14 '15 at 12:42

Mahdi Sabori

77
3
12

While you filter by id, filtering by quiz is just overhead. – Giorgi Nakeuri Jun 14 '15 at 12:43

MySQL: return first row from join

2 Answers2