Calculate the second highest salary of an employee

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

Comments or Responses

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,

Thank you for your advices .


Thank you,

Login to post response