Problem: Find Nth highest salary in Minimal time.
Solution: Interviewer often asks to find Nth Highest Salary in an interview. Many solutions are available on internet to do so. Couple of commonly used solutions are Solution 1 & Solution 2.
All these solutions are bit slow. But by using Common Expression Tables (CTE) you can find the Nth highest salary very efficiently and impress your interviewer.
- Create a Sample Table and Insert Sample Data in it:
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
After creating the table, execute the above code to insert the data into the table as follows:
INSERT INTO #T1 VALUES (1, ‘pds’, 10000)
INSERT INTO #T1 VALUES (2, ‘vicky’, 10000)
INSERT INTO #T1 VALUES (3, ‘divyan’, 30000)
INSERT INTO #T1 VALUES (4, ‘deepak’, 40000)
INSERT INTO #T1 VALUES (5, ‘anu’, 50000)
INSERT INTO #T1 VALUES (6, ‘jai’, 60000)
INSERT INTO #T1 VALUES (7, ‘abhi’, 20000)
INSERT INTO #T1 VALUES (8, ‘disha’, 40000)
INSERT INTO #T1 VALUES (9, ‘anika’, 60000)
INSERT INTO #T1 VALUES (10, ‘vijay’, 50000)
To find the highest salary we can simply use the Max function as given below:
Select Max(Salary) as [Highest Salary] from T1
- Following Query will Find Nth Highest Salary in minimal time:
declare @n int;
set @n =4
with cte as
(
SELECT name, salary,
dense_rank() OVER( ORDER BY salary DESC) AS rowid
FROM #t1
)
SELECT *
FROM cte
WHERE rowid = @n
This will create row number for each row after it has been sorted by salary in the descending order.
- Comparison Stats with Other Solutions:
I like this post, enjoyed this one thankyou for posting .
Very thanks Sabra for the feedback