Select Find Get Second Third Nth Highest Salary Record Row In Ms Sql Server. This is most frequentky asked question how to select or get nth highest record or second third row/record from any column of sql table.
for example select get or fetch 2nd (second highest) or nth highest salary of employee or 10th highest record from the table.
There are various ways to achieve this result, i've mentioned few here.
I have created Employee table with following schema.
1st method
To select 2nd highest salary or record we can use following query.
2nd method
To select 3rd highest salary or record we can use following query.
These queries holds good untill we are selecting only salary column and fails when we want to select all the columns or few more columns with salary as salary can be same for more then one employees or records.
For example if we change the first query to select 2nd highest salary with all the columns of table, output would be undesirable as shown below.
To select all columns we can use queries mentioned below.
This query will give 4th highest salary record but will show only 1 highest record if even if there are multiple duplicate salary records.
These 2 queries will select 4th highest salary with duplicate records.
We can also use sql ranking function to get desired result as follows.
There are various ways to achieve this result, i've mentioned few here.
I have created Employee table with following schema.
1st method
To select 2nd highest salary or record we can use following query.
SELECT TOP 1 [Salary] FROM ( SELECT DISTINCT TOP 2 [Salary] FROM [dbo].[Employee] ORDER BY [Salary] DESC ) temp ORDER BY [Salary]
2nd method
To select 3rd highest salary or record we can use following query.
SELECT TOP 1 [Salary] FROM ( SELECT TOP 3 [Salary] FROM [dbo].[Employee] e1 GROUP BY e1.Salary ORDER BY [e1].[Salary] DESC) e2 ORDER BY [Salary]
These queries holds good untill we are selecting only salary column and fails when we want to select all the columns or few more columns with salary as salary can be same for more then one employees or records.
For example if we change the first query to select 2nd highest salary with all the columns of table, output would be undesirable as shown below.
SELECT TOP 1 [Salary],[EmployeeName] FROM ( SELECT DISTINCT TOP 2 [Salary], [EmployeeName] FROM [dbo].[Employee] ORDER BY [Salary] DESC ) temp ORDER BY [Salary]
To select all columns we can use queries mentioned below.
This query will give 4th highest salary record but will show only 1 highest record if even if there are multiple duplicate salary records.
SELECT TOP 1 * FROM [dbo].[Employee] WHERE [Salary] NOT IN ( SELECT DISTINCT TOP 3 [Salary] FROM [dbo].[Employee] ORDER BY [Salary] DESC ) ORDER BY [Salary] DESC
These 2 queries will select 4th highest salary with duplicate records.
SELECT * FROM [dbo].[Employee] WHERE [Salary] = ( SELECT MAX([Salary]) FROM [dbo].[Employee] WHERE [Salary] NOT IN ( SELECT DISTINCT TOP (4-1) [Salary] FROM [dbo].[Employee] e1 ORDER BY [Salary] DESC ) )
SELECT * FROM Employee E1 WHERE (4-1) = ( SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary > E1.Salary)
We can also use sql ranking function to get desired result as follows.
SELECT * FROM ( SELECT DENSE_RANK() OVER(ORDER BY [Salary] DESC)AS RowId, * FROM [dbo].[Employee] ) AS e1 WHERE e1.RowId = 4
