I Have Employee table having column name as ID,SALARY
how to get second max salary from employee table with id
ex
ID SALARY
1 20000
7 37000
2 5000

Answers were Sorted based on User's Feedback



I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / elumalai.k

select id,max(salary)from employee where salary<(select
max(salary)from employee)

Is This Answer Correct ?    15 Yes 6 No

I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / ashish

hey u can try This It defenately work...It for The nth
position mean for the N th node try it out.......



SELECT *
FROM emp A
WHERE (n-1) = (
SELECT COUNT(DISTINCT(B.salary))
FROM emp B
WHERE B.salary > A.salary)

Is This Answer Correct ?    5 Yes 0 No

I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / elumalai.k

select top1 id,salary from employee
where
(salary<(select max (salary) As sal from employee As Empp))
orderby salary desc.

Is This Answer Correct ?    5 Yes 0 No

I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / kavitha

select * from emp as a

where sal < 2 from emp as a1
on
(
a.idno=a1.idno
)

Is This Answer Correct ?    4 Yes 0 No

I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / lekhrajdeshmukh

select top 1 id,salary from(select top 2 id,salary from
employee group by id,salary order by salary desc)a order by
salary asc

Is This Answer Correct ?    5 Yes 1 No

I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / sivanandam

SELECT ID,MAX(SALARY) FROM EMP
WHERE SALARY < SELECT MAX(SALARY) FROM EMP

Is This Answer Correct ?    6 Yes 3 No

I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / bobby

SELECT sm ,custid ,ordyear,amount FROM
(SELECT sm ,custid ,ordyear,amount,Rank() OVER (ORDER BY
amount desc)as Rank
FROM dbo.OrderCust)p where rank=2


This is used to select all the rows having the second max
salary.

Is This Answer Correct ?    3 Yes 0 No

I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / tusar

select distinct (a.sal),empno from emp a
where &N = (select count (distinct(b.sal))
from emp b where a.sal <= b.sal);

Is This Answer Correct ?    3 Yes 0 No

I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / chan

SELECT ID, SALARY FROM
(
SELECT ID, SALARY, ROW_NUMBER() OVER (ORDER BY SALARY) AS RN
FROM EMPLOYEE
)
WHERE RN = 1

(YOU CAN CHANGE VALUE OF RN = ANY NUMBER AND YOU WILL GET
THAT VALUE)

Is This Answer Correct ?    3 Yes 1 No

I Have Employee table having column name as ID,SALARY how to get second max salary from employee ta..

Answer / deepa

drop table #temp
select top 2 identity(int,1,1) as SlNo, *
into #temp from employee order by salary desc
select * from #temp where SlNo = 2

Is This Answer Correct ?    3 Yes 1 No

Post New Answer

More SQL Server Interview Questions

what is the difference between group and having give an example with query and sample output

7 Answers  


what is nonclustered index

0 Answers  


What are the different types of replication are there in sql server 2000?

0 Answers  


What do you know about normalization and de- normalization?

0 Answers  


explain databases and sql server databases architecture? : Sql server database administration

0 Answers  






What is triggers in ms sql server?

0 Answers  


What is the purpose of linked server configuration in sql server?

0 Answers  


Why truncate is ddl?

0 Answers  


Write the SQL query to drop, truncate and delete table.

0 Answers   HPCL, Hughes Systique Corporation, Ittiam Systems,


What is the use of attributehierarchyvisible ? : sql server analysis services, ssas

0 Answers  


How to connect of datebase with sql express.?

0 Answers   MCN Solutions,


what is the difference between procedure and function.

4 Answers   Thomson,


Categories