Get row with max(column) in mysql

Get row with max(column) in mysql

problem:

my table is:

user col1 col2 col3

1    1    2    3

1    3    4    5

2    2    3    1

3    1    1    3

3    2    4    6

4    5    1    5

So if I run

select user, col1, col2, max(col3) as col3

from table

group by user

order by user;

I would get

user col1 col2 col3

1    1    2    5

2    2    3    1

3    1    1    6

4    5    1    5

So the max value of col3 is correct, but it doesn't get the correct row of that value.

What I want is to get the max value of a column and return that row for each user. If there are multiple max values that it should return all users, even if it has same user id.

solution:

select x.user, y.col1, y.col2, x.col3

from (

  select user, max(col3) as col3

  from table

  group by user

) x

inner join table y on y.user = x.user and y.col3 = x.col3

order by x.user