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.