# Calculate the second highest salary of an employee

Posted by Srilu.Nayini577 under Sql Server category on | Points: 40 | Views : 6138
select sal from employee where sal<(select max(sal) from employee)

Posted by: Gopesh9 on: 8/14/2012 Level:Starter | Status: [Member] | Points: 10
It will give all the salary which will be less than the maximum salary. If You want only the 2nd highest salary then you will write

`select max(sal) from employee where sal<(select max(sal) from employee)  `

Posted by: Srilu.Nayini577 on: 8/14/2012 Level:Starter | Status: [Member] | Points: 10
sorry for that.Actually i wrote squery as:

select max(sal) from employee where sal<(select max(sal) from employee)

by mistaken max keyword will be deleted.

Thank you,
Posted by: Vikash on: 8/14/2012 Level:Starter | Status: [Member] | Points: 10
this is ok for second highest salary,but if i have to select 8th highest salary or 9th highest salary then this will not be feasible.......................
Posted by: Gopesh9 on: 8/14/2012 Level:Starter | Status: [Member] | Points: 10
@Vikash, You have to wite the C# code for that if you want the 8th or 9th highest salary...
Posted by: Srilu.Nayini577 on: 8/14/2012 Level:Starter | Status: [Member] | Points: 10
By sql server only we can calculate 8 th or 9th etc salary

Syntax:

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

Example:
If you want to calculate 9th highest sal put n=9;

SELECT TOP 1 sal
FROM (
SELECT DISTINCT TOP 9 sal
FROM Emp1
ORDER BY sal DESC) a
ORDER BY sal

If u want to find 10 ,u can put 10 inplace of n.

I think no need of c# code
Posted by: Srilu.Nayini577 on: 8/14/2012 Level:Starter | Status: [Member] | Points: 10
By sql server only we can calculate 8 th or 9th etc salary

Syntax:

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

Example:
If you want to calculate 9th highest sal put n=9;

SELECT TOP 1 sal
FROM (
SELECT DISTINCT TOP 9 sal
FROM Emp1
ORDER BY sal DESC) a
ORDER BY sal

If u want to find 10 ,u can put 10 inplace of n.

I think no need of c# code
Posted by: Gopesh9 on: 8/14/2012 Level:Starter | Status: [Member] | Points: 10
@srilu- By doing this you are giving extra load to the server, in a real time environment where you want to customize more and more so that the time will be minimized you have to use C# code in this case.
Posted by: Srilu.Nayini577 on: 8/14/2012 Level:Starter | Status: [Member] | Points: 10
Hi Gopesh,