what is syntex second or third highest salary.
thanks & Regards
Dhirendra sinha
Answers were Sorted based on User's Feedback
Answer / sagar dhande
select top 2(salary) from (select top 3 (salary) from
dbo.employee order by salary desc)
order by salary
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / praveen singh
select min(salary) from salary where salary in(select top 3
salary from salary order by salary desc)
Is This Answer Correct ? | 3 Yes | 2 No |
Answer / satish
select max(sal) from emp e1 where 1< (select count(distinct sal) from emp e2 where e1.sal <= e2.sal)
To make it generic if u want N'th highest salary;
select max(sal) from emp e1 where (N-1)< (select count(distinct sal) from emp e2 where e1.sal <= e2.sal)
And to get the N'th min sal :
select min(sal) from emp e1 where (N-1)< (select count(distinct sal) from emp e2 where e1.sal >= e2.sal)
Is This Answer Correct ? | 1 Yes | 0 No |
with Topsal (EmpID,Ename,Sal,Dno,Topsal) as
(select *,row_number() over(order by sal) Topsal from emp)
select top 2 EmpID,Ename,Sal,Dno from Topsal order by sal desc
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / vikas kant
---To get 2nd highest salary----
select Top 1 salary from Emp where marks in(select top 2
salary from Emp order by salary desc)
---To get 3rd highest salary----
select Top 1 salary from Emp where marks in(select top 3
salary from Emp order by salary desc)
Regards Vikas Kant
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / anil tiwari
select Top2(salery)from (select Top3 (salery) from tab1
order by salery DESC)order by salery.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / dipti saxena
select sal,rownum from (select sal from emp
order by sal desc)
where rownum<=3
MINUS
select sal,rownum from (select sal from emp
order by sal desc)
where rownum<=1
Is This Answer Correct ? | 0 Yes | 4 No |
how we can count records as a group of days like sum of records for(four mondays),(four tuesday)........ in a month. group the column for weekdays.
Why do we use functions?
What are the different types of subquery?
Is it possible for a stored procedure to call itself or recursive stored procedure?
What is Files and Filegroups in SQL Server & it's implementation.
How you can find out if an index is useful to the optimizer?
Define normalisation?
What is a unique index?
How will you fine tune a stored procedure or what are the steps that should be taken to fine tune or optimize a stored procedure?
How to generate create procedure script on an existing stored procedure?
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
11 Answers World Tech, Yardi Software,
Which command using Query Analyzer will give you the version of SQL server and operating system?