SQL Trivia – Find second (or nth) Highest Salary or Marks
At a beginner level this is the most basic and widely asked SQL Interview Question, and I’ve seen many people talk about this when you ask them to ask a basic SQL question.
Or, I could say it’s a tollgate to judge if a candidate knows basic SQL or not 🙂
So, here on demand of lot of people who ping me or email me for this, I’ve drafted this post on various ways or Queries to get the desired results.
–> I’ll create a dummy dbo.Employee table and populate with some dummy records (all these are my school/college friends):
USE tempdb GO CREATE TABLE dbo.Employee ( EmpID INT PRIMARY KEY NOT NULL, EmpName VARCHAR(100), Salary Money ) GO INSERT INTO dbo.Employee SELECT 1, 'Manoj', 20000 UNION ALL SELECT 2, 'Saurabh', 50000 UNION ALL SELECT 3, 'Kanchan', 30000 UNION ALL SELECT 4, 'Hema', 10000 UNION ALL SELECT 5, 'Bhanu', 60000 UNION ALL SELECT 6, 'Prakash', 80000 UNION ALL SELECT 7, 'Vivek', 40000 UNION ALL SELECT 8, 'Ramesh', 55000 UNION ALL SELECT 9, 'Kapil', 65000 UNION ALL SELECT 10, 'Dev', 90000 GO select * from dbo.Employee order by Salary DESC GO
–> We will check for these conditions, 2nd highest & nth highest salary records from the above query, as shown below:
2nd highest is: 6, Prakash, 80000.00 5th highest is: 8, Ramesh, 55000.00 7th highest is: 7, Vivek, 40000.00
–> 2nd Highest Salary:
1. For SQL Server 2005 and +
;WITH CTE AS ( SELECT EmpID, EmpName, Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN FROM dbo.Employee ) SELECT EmpID, EmpName, Salary FROM CTE WHERE RN = 2 GO
2. For SQL Server 2000
SELECT TOP 1 EmpID, EmpName, Salary FROM (SELECT TOP 2 EmpID, EmpName, Salary FROM dbo.Employees ORDER BY Salary DESC ) X ORDER BY Salary ASC GO
–> nth Highest Salary: Just parametrize the numbers
1. For SQL Server 2005 +
DECLARE @n INT = 5 ;WITH CTE AS ( SELECT EmpID, EmpName, Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN FROM dbo.Employee ) SELECT EmpID, EmpName, Salary FROM CTE WHERE RN = @n GO
2. For SQL Server 2000
DECLARE @n INT = 5 SELECT TOP 1 EmpID, EmpName, Salary FROM (SELECT TOP (@n) EmpID, EmpName, Salary FROM dbo.Employees ORDER BY Salary DESC ) X ORDER BY Salary ASC GO --// Final Cleanup DROP TABLE dbo.Employee GO
There are lot other ways also to get the same results and you can come up with different Queries with more optimizations.
Please do let me know if you have any comments and suggestions, thanks!