Query in Database

Hi,

try this.

select * from (
select top 2 employeeID,salary,ROW_NUMBER()OVER(ORDER BY salary  DESC) as RowNum
from employee) A
where A.RowNum = 2

hope this help.

Hi , I think you can use rank()over to make it easier to implement.

try my sample .  u can copy it in your query browser directly.

create table #test (name varchar(10),salary int)
insert into #test select 'a',80
insert into #test select 'b',65
insert into #test select 'c',80
insert into #test select 'd',75
insert into #test select 'e',95
insert into #test select 'f',88
insert into #test select 'g',90  -- this is the second highest


select
  *
from
  (select name , salary ,rank()over(order by salary desc) n from #test)t
where
  n=2
 
drop table #test

Hi,

Here is an example to find out nth highest salary from Employee table

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)

Hi Aaron,


Select max(salary) from employee where salary NOT IN
(Select max(salary) from employee)

Or

select distinct(salary)  from employee A where 2=(select count(distinct(salary)) from employee B where A.salary<=B.salary)



Thanks

Hi Aaron,

use query like below..........


select distinct top 1 salary from Employee where salary not in (select top 1 salary from Employee)










Thanks,
Pravin.