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.
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!
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|