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 |
Determine when an index is appropriate?
How do you use a subquery to find records that exist in one table and do not exist in another?
What is an indice?
What is user defined stored procedures?
How to choose all records from the table?
How to find out name of all employees who has salary less than 200 Rs.?
mention different types of relationships in the dbms?
What is the user of Primary key?
What are the steps you can take to avoid “deadlocks”?
If i am handling 150 servers then how to check the active jobs of all the servers?
Write the Syntax for Cursors.
2 Answers CarrizalSoft Technologies, HP,
How to Create Login and User through query in Sql server 2005.and also how to delete Login and User through query?