Query in Database

Question

Hi...... I am working in ADO.NET and face a problem in query. How can we find out the second highest salary from any table and also define about where clause?

Jignesh Trivedi · Answer

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.

Shen Hengbin · Answer

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

Mukesh Kumar · Answer

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)

Satyapriya Nayak · Answer

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 Thanks

Pravin More · Answer

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.

Query in Database

Answers (5)