Write a query to get 2nd maximum salary in an employee table ?
Answers were Sorted based on User's Feedback
Answer / omkar hendre
select distinct(sal) from emp a where 2=(select count(distinct(sal)) from emp b where a.sal <=b.sal)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / swapnil tikale
Select top 1 salary from (select distinct top 2 salary from employee order by salary desc) as sal order by salary ;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / prem chhappesh
select max(salary) from emoplyee where salary not in(select max(salary) from employee)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / tejasvita dhuri
select id,salary from (select id,salary,ROW_NUMBER()over (order by salary desc) as rowcol from emp)A
where A.rowcol=2
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / anita prasad
SELECT MIN(SAL) FROM (SELECT DISTINCT * FROM EMP ORDER BY DESC) WHERE ROWNUM<3;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / satish
select rownum,employee_id,first_name,max(salary) from
employees
group by rownum,employee_id,first_name
having rownum<=2
order by rownum
Is This Answer Correct ? | 5 Yes | 6 No |
Answer / deven
select top 1 salary from emp where salary <>(
select top 1 salary from emp order by salary desc) order by
salary desc
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / deepa
select min(salary) from (select salary from table_name order by salary desc) where rownum<3;
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / niladri saha
into my above solution,u can get whatever position u want,only by giving "n" at the place of "2" at row num. That the right way to write the query. That u can use in dynamically.
Thanks,
like if ur require ment to print empnames of having 3rd max salary,then put 3 at rownum.
Niladri Saha.
Is This Answer Correct ? | 0 Yes | 1 No |
What is form and report?
What does select count (*) mean in sql?
What is dbo in sql?
what is normalization? : Sql dba
How does sql store data?
trans_id trans_date trans_amt debit_credit_indicator 001 01-JAN-13 1099 cr 001 12-JAN-13 500 db 002 24-FEB-13 400 db 002 23-MAR-13 345 cr 001 18-APR-13 800 cr 002 15-MAR-13 600 db 001 12-FEB-13 200 cr i want like this output trans_id trans_date trans_amt debit_credit_indicator 001 JAN 1599 cr no.of trans 2 i want trans_id and trans_date like 'JAN' or 'FEB' etc, trans_amt i want all credit amount - debit amount per each trans_id. and debit_credit_indicator and no.of transactions in a month.
what are the differences among these table level lock modes - IN SHARE MODE, IN SHARE UPDATE MODE, IN EXCLUSIVE MODE ?
consider a table which contain 4 columns,ename,eno,sal and deptno, from this table i want to know ename who having maximum salary in deptno 10 and 20.
what are the authentication modes in sql server? How can it be changed? : Sql dba
what is global variable in package
How do I partition in sql?
Explain the usage of WHERE CURRENT OF clause in cursors ?