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
select id,max(salary)from employee where salary<(select
max(salary)from employee)
Is This Answer Correct ? | 15 Yes | 6 No |
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 |
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 |
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 |
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 |
Answer / sivanandam
SELECT ID,MAX(SALARY) FROM EMP
WHERE SALARY < SELECT MAX(SALARY) FROM EMP
Is This Answer Correct ? | 6 Yes | 3 No |
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 |
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 |
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 |
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 |
what is the difference between group and having give an example with query and sample output
what is nonclustered index
What are the different types of replication are there in sql server 2000?
What do you know about normalization and de- normalization?
explain databases and sql server databases architecture? : Sql server database administration
What is triggers in ms sql server?
What is the purpose of linked server configuration in sql server?
Why truncate is ddl?
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
How to connect of datebase with sql express.?
what is the difference between procedure and function.