jaffer jaffer - 6 months ago 3x
SQL Question

How do i get max salary row , group by country

country | salary | name | adress
India | 10000 | ch1 | something
japan | 20000 | ch2 | nothing
india | 25000 | ch3 | hjsdj
japan | 30000 | ch4 | hjhjhj

I need to get max salary in japan and also in india with adress, name.


Have a sub-query that returns each country's max salary. Join with that result to get the users with that max salary.

select t1.*
from tablename t1
join (select country, max(salary) as max_salary
      from tablename group by country) t2
  on t1.country = t2.country and t1.salary = t2.max_salary

Will return both users if it's a tie (i.e. several users with same maximum salary.)