Display Second or N th. Salary from Table in SQL Server

Background

I have given a name to my blog as 'Display 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

Now insert the some record into the table that shown in below and

select * from employee

The output will be look like as follows..

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...

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 Displyning 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 quesry is

SELECT TOP 1 salary

FROM

(

SELECT DISTINCT TOP 2 salary

FROM employee

ORDER BY salary DESC) a

ORDER BY salary

The above query displayes 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 displayes 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 displayes the fourth highest salary.

Simmilarly 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.