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 employeeIDsalaryROW_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.