1

I want to return the name of those persons who have the highest salary city-wise. This is my table:

employee table

I was able to print the highest salary city-wise by using the GROUP BY clause. But how to return the name of those employees also who have the highest salary. I want to return something like this:

targetQuery. as these persons have the highest salary city-wise.

spraj
  • 65
  • 9

2 Answers2

3

You could use the rank window function to rank the employees per city, and then wrap that query with another query to get only the highest one per city:

SELECT ename, city, salary
FROM   (SELECT ename, city, salary,
               RANK() OVER (PARTITION BY city ORDER BY salary DESC) AS rk
        FROM   employee) t
WHERE  rk = 1
Mureinik
  • 297,002
  • 52
  • 306
  • 350
0

In Postgres, I strongly recommend distinct on for this purpose:

select distinct on (city) e.*
from employee e
order by city, salary desc;

distinct on is a Postgres extension specifically designed for this type of problem.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786