jaffer jaffer - 7 months ago 9
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.

Answer

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.)