Get Second or N th Salary from Table in SQL Server

I have given a name to my blog as 'Get Second or Nth. Salary from Table in sql server' because any interview this question is always asked, also according to my personal experience I have faced many interview and the same question is asked in my all interviews so to help freshers i have decided to write this blog,so let us start..
Create a one table named employee as follows
emptable.png
Now insert the some record into the table that shown in below and 
selectfrom employee
The output will be look like as follows..

recordtable.png

Now let write a query to retrieve second highest salary from employee table 

select MAX (salary) as Salary from employee

where salary < 

select MAX (salary) as Salary from employee   )

run the above query the output will be as follows...

second.png
From the above example its clear that ,the above query displays the second highest salary from table ,in the above query the nested query is executed first which retrieves the highest salary and because of less than condition in where clause the first query  compares with second query and retrieves less than highest salary that is second.
Now, if interviewer asked to display 3 rd highest salary at that time i will write another nested query with less than where condition but what if interviewer asked any number of salary at that time if i write number of nested queries according salary then its become complicated and also interviewer does not like this ,so use following query to retrieve any number of salary from table.

Query for Displaying N th. salary

SELECT TOP 1 salary
FROM 
(
SELECT DISTINCT TOP N salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
In the above query just replace N with number that which no. of salary or number you wants to retrieve from table .suppose

To retrieve 2nd highest salary then query is
SELECT TOP 1 salary
FROM 
(
SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
The above query displays the second highest salary.
To display 3 rd Highest salary
SELECT TOP 1 salary
FROM 
(
SELECT DISTINCT TOP 3 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
The above query displays the third highest salary.

To display 4 th highest salary
SELECT TOP 1 salary
FROM 
(
SELECT DISTINCT TOP 4 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
The above query display's the fourth highest salary. Similarly you can display any number of salary as shown in above.
Summary
I hope this small blog is useful for all job seekers and freshers,if you have any suggestion then please contact me.

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode