How to find highest salary in sql

 

udayarumilli_highest

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.

Posted in SQL Development | Tagged , , , , , , , , | 2 Comments
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
e-pvp
e-pvp
10 years ago

Wow, wonderful blog layout! How lengthy have you been running a blog for? you make running a blog look easy. The total look of your site is fantastic, let alone as} the content!

ddos vps
ddos vps
10 years ago

Sweet blog! I found it while searching on Yahoo News. Do you have any suggestions on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there! Appreciate it|