CODEWITHSUNDEEP
sqlamazon-redshift
qazwsx123
qazwsx123

Reputation: 237

SQL Return rows with highest value for a given field

I was practising some SQL with this example data:

employee_data

salary    first_name    department
50000     Adam          Sales
27000     Beth          HR
70000     Dave          IT
52000     Amy           Sales
etc...

I wrote a query to return the records with the highest salary for each department. However, for some of the departments there were two or more people tied for the highest salary. So I wrote this short script:

WITH cte AS( 
SELECT salary, first_name, department, ROW_NUMBER() OVER(PARTITION BY department) AS rownum
FROM employee_data
WHERE salary IN (SELECT MAX(salary) FROM employee_data GROUP BY department)
)

SELECT *
FROM cte
WHERE rownum = 1

This does return the correct output being only taking one record for each department where there is a tie for the highest salary. But I was wondering, is there a more efficient way to do it? With less lines or without the need to include a CTE.

Upvotes: 0

Views: 999

Answers (2)

mani bharataraju
mani bharataraju

Reputation: 162

You can add filter where Rank() and row_number() are 1 for a department

select 
first_name,
salary,
department_name
from
(Select
first_name,
salary,
department_name
row_number()over(partition by department_name order by salary desc) as rn,
rank()over(partition by department_name order by salary desc) as rnk
from table)a
where rn=1 and rnk=1;

Upvotes: 0

ggordon
ggordon

Reputation: 10035

The RANK function is more suited for this scenario eg

WITH cte AS( 
    SELECT 
        salary, 
        first_name, 
        department, 
        RANK() OVER(
             PARTITION BY department
             ORDER BY salary DESC
        ) AS rnk
    FROM 
        employee_data
)

SELECT *
FROM cte
WHERE rnk = 1

Let me know if this works for you.

Upvotes: 1

Related Questions