SQL command for finding the second highest salary

HI, Can u tell me the syntax of the SQL command which gives as output the second highest salary from a range of salaries stored in the employee table. A description of开发者_如何学C the SQL commnd will be welcomed... Please help!!!

select min(salary) from 
(select top 2 salary from SalariesTable order by salary desc)
as ax

This should work:

select * from (
 select t.*, dense_rank() over (order by salary desc) rnk from employee t
) a 
where rnk = 2;

This returns the second highest salary.

dense_rank() over is a window function, and it gives you the rank of a specific row within the specified set. It is standard SQL, as defined in SQL:2003.

Window functions are awesome in general, they simplyfy lots of difficult queries.

Slightly different solution:

This is identical except that returns the highest salary when there is a tie for number 1:

select * from (
 select t.*, row_number() over (order by salary desc) rnk from employee t
) a 
where rnk = 2;

Updated: Changed rank to dense_rank and added second solution. Thanks, IanC!

with tempTable as(
    select top 2 max(salary) as MaxSalary from employee order by salary desc
) select top 1 MaxSalary from tempTable

description:

1. select the top 2 maximum salaries
2. order them by desc order ( so the 2nd highest salary is now at the top)
3. select the top 1 from that

another approach:

select top 1 MaxSalary from (
    select top 2 max(salary) as MaxSalary from employee order by salary desc
)

Here's some sample code, with proof of concept:

declare @t table (
    Salary int
)

insert into @t values (100)
insert into @t values (900)
insert into @t values (900)
insert into @t values (400)
insert into @t values (300)
insert into @t values (200)

;WITH tbl AS (
    select t.Salary, DENSE_RANK() OVER (order by t.Salary DESC) AS Rnk
    from @t AS t
)
SELECT *
FROM tbl
WHERE Rnk = 2

DENSE_RANK is mandatory (change to RANK & you'll see).

You'll also see why any SELECT TOP 2 queries won't work (without a DISTINCT anyway).

You don't specify the actual SQL product you're using, and the query language varies among products. However, something like this should get you started:

SELECT salary FROM employees E1 
    WHERE 1 = (SELECT COUNT(*) FROM employee E2 WHERE E2.salary > E1.salary)

(thanks to fredt for the correction).

Alternatively (and faster in terms of performance) would be

SELECT TOP 2 salary FROM employees ORDER BY salary DESC

and then skipping the first returned row.

An alternative (tested):

select Min(Salary) from (
    select distinct TOP (2) salary from employees order by salary DESC) AS T

This will work on any platform, is clean, and caters for the possibility of multiple tied #1 salaries.

Select top 1 * from employee where empID in (select top 2 (empID) from employee order by salary DESC) ORDER BY salary ASC

Explanation:

select top 2 (empID) from employee order by salary DESC would give the two records for which Salary is top and then the whole query would sort it these two records in ASCENDING order and then list out the one with the lowest salary among the two.

EX. let the salaries of employees be 100, 99, 98,,50.

Query 1 would return the emp ID of the persons with sal 100 and 99

Whole query would return all data related to the person having salary 99.

 SELECT Salary,EmpName
 FROM
   (
   SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As Rank
   FROM EMPLOYEE
   ) A
   WHERE A.Rank=n;

where n is the number of highest salary u r requesting the table. Ucan also use DenseRank() function in place of ROW_NUMBER().

Thanks, Suresh

An easier way..

select MAX(salary) as SecondMax from test where salary !=(select MAX(salary) from test)