udayarumilli.com

How to find highest salary in sql

We had a situation where we have to find out all the details of a product with the Nth highest price Note: Some of the products are in the same price range.

Here I am demonstrating with employee table. Create the table “Employee” from the below script.

USE [udayarumilli];
GO
CREATE TABLE [dbo].[Employee](
    [empid]     [int] IDENTITY(1,1) NOT NULL,
    [name]      [nvarchar](20) NOT NULL,
    [birthdate] [datetime] NOT NULL,
    [Salary]    [int] NOT NULL
) ON [PRIMARY];
GO
INSERT INTO [Employee] ( name,birthdate,Salary)
VALUES ('Sara Davis',1958-12-08,120000),
('Don Funk',1962-02-19,98000),
('Judy Lew',1973-08-30,76000),
('Yael Peled',1947-09-19,76000),
('Sven Buck',1965-03-04,102000),
('Paul Suurs',1973-07-02,110000),
('Russell King',1970-05-29,69000),
('Maria Cameron',1968-01-09,72000),
('Zoya Dolgopyatova',1976-01-27,89000)
GO

Now let’s check the employee salaries using the below query.

SELECT      Salary
FROM         Employee
ORDER BY  salary DESC;
GO

From the table we can say that highest salary is: 120000 and lowest salary is: 69000

Now we‘ll see the different options how can get the employee details for Nth highest salary.

There are mainly two ways we can accomplish this as below. We’ll find the 3rd highest salary.

-- Traditional way to find the 3rd highest salaried employee details

SELECT TOP 1 name,salary
FROM (
        SELECT DISTINCT TOP 3 name,salary
        FROM        employee
        ORDER BY    salary DESC) e
ORDER BY salary
 

-- Using ROW_NUMBER() to find the 3rd highest salaried employee details

SELECT    name,salary
FROM(
        SELECT name,Salary,
               row_number() over(order by salary desc) as place
        FROM   employee) e
WHERE e.place=3

From the data yes we can confirm these are giving the 3rd highest salaried employee details.

 From the above queries if you want to find the 4th highest just replace 3 with 4 and if Nth highest required replace 3 with N.

 Now we’ll try to find out the 6th highest salary using the same queries.

 -- Traditional way to find the 6th highest salaried employee details

SELECT TOP 1 name,salary
FROM (
        SELECT DISTINCT TOP 6 name,salary
        FROM        employee
        ORDER BY    salary DESC) e
ORDER BY salary

-- Using ROW_NUMBER() to find the 6th highest salaried employee details
SELECT    name,salary
FROM(
        SELECT    name,Salary,row_number() over(order by salary desc) as place
        FROM    employee) e
WHERE e.place=6

Here is the problem. If you can observe there are two employees with the same salary

But these queries are retrieving only one record that is 6th row: Judy Lew. But there are two employees with the same salary which is 6th highest in the group.

If we try to capture the 7th highest it gives us the wrong result. It might be 6th row or 7th row depends on the ORDER BY clause.  To avoid this we have to change the query to use DENSE_RANK() instead of ROW_NUMBER().

-- Using DENSE_RANK() to find the 6th highest salaried employee details

SELECT  name,salary as '6th_Highest'
FROM(
        SELECT  name,Salary,
                DENSE_RANK() over(order by salary desc) as place
        FROM    employee) e
WHERE e.place=6

-- Using DENSE_RANK() to find the 3rd highest salaried employee details
SELECT    name,salary AS '3rd_Highest'
FROM(
        SELECT  name,Salary,
                DENSE_RANK() over(order by salary desc) as place
        FROM    employee) e
WHERE e.place=3

Now we can see all the employees who are at the 6th highest salary are included in the result set.