How do I get value by other column max value for particular user in SQL

Question

I have next table:

(AUTO_INCREMENT)
     |
     V
survey_id   | user_id| tries |  results
------------|--------|---------------
    1       |   10   |  1    |   A
    2       |   10   |  2    |   B
    3       |   11   |  1    |   C
    4       |   10   |  3    |   D
    5       |   11   |  2    |   E

I have, for example, user_id = 10 and I want to get the value from the result row where tries value has the max result for this user, in this case it is going to be D result.

How could I do this query in SQL?

Answer 1

You can achieve this with simple subquery

select * from tbl t
where user_id = '10'
  and tries = (select max(tries) from tbl ti where t.user_id = ti.user_id )