SQL with Manoj

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!