CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Michael Sodovski
Michael Sodovski

Reputation: 7

How can I select max value and second max value from table and order by city id?

I have this employees table. I need to write an SQL query that will bring me the max salary of an employee and the second highest salary of an employee by city id (id is linked to a table with cities. there are 5 cities.)

Employees table

My query looks like this:

select MAX([dbo.Employees].Salary) as Salary from [dbo.Employees] 
where [dbo.Employees].Salary not in(select MAX([dbo.Employees].Salary) from [dbo.Employees])
UNION select MAX([dbo.Employees].Salary) from [dbo.Employees] group by [dbo.Employees].Id

I try to bring the highest and exclude the highest but it suppose to bring overall 7 values but it brings only 5. (because there are 5 cities but the 5th is not in use so there are 4 cities and 2 employees in each city except 1 that has only 1 employee so the query suppose to bring me 2 pairs of employees per city = 6, and one of the cities has only 1 employee so it will bring the only possible value. overall 7. )

another problem is that I don't know how to make in bring 2 columns - one for the id of the cities and the second for the salaries themselves because it tells me that something about the group by doesn't work.

Upvotes: 0

Views: 415

Answers (1)

MatBailie
MatBailie

Reputation: 86735

You can use ROW_NUMBER() to create a sequence for each city, with the highest salary getting value 1, second highest getting value 2, etc. Then you just need a WHERE clause.

WITH
  ranked AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY Salary DESC)  AS city_salary_id
  FROM
    dbo.Employees
)
SELECT
  *
FROM
  ranked
WHERE
  city_salary_id IN (1, 2)

If multiple people are tied with the same salary, it will arbitrarily pick the order for you, and always return (at most) 2 employees per city.

Adding a column to the ORDER BY lets you be more specific about how to deal with ties, such as ORDER BY Salary DESC, id ASC will prioritise the highest id in the even of a tie.

Changing to RANK() will give tied Salaries the same rank, and so will return more than two employees if there are ties.

Upvotes: 2

Related Questions