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