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 many types of built in functions are there in sql server 2012?
Wht is the difference between stored procedure and trigger
What do you mean by data integrity?
What is the use of RDBMS?
What is the downside of using udf?
what is unique and xaml nonclustered index
If we use where clause in the left outer join then how the query would behave/act?
What are number line correlation administrators will use while working with a subquery?
What command is used to rename the database?
write down the sql query? Table Name : emp1 empid empname 1 bala 2 guna Table Name : emp2 empid empname 1 <Null> 2 <Null> Solution : emp1 names are updated in emp2, write a query?
How to test odbc dsn connection settings?
Can group by be used without aggregate functions?