CODEWITHSUNDEEP
sqlaggregate-functions
jaffer
jaffer

Reputation: 23

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.

Upvotes: 2

Views: 57

Answers (2)

Serg
Serg

Reputation: 22811

If by chance your DBMS supports ROW_NUMBER() OVER() function you may try

select * from
  (select ROW_NUMBER() OVER(PARTITION BY country ORDER BY salary DESC) as rn
    , country, salary as max_salary, name, adress
  from tablename
  ) t
where rn = 1

Note, contrary to jarlh's query it will return only one arbitrary row of equal top salaries rows for the country.

Upvotes: 0

jarlh
jarlh

Reputation: 44786

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

Upvotes: 4

Related Questions