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 |
How do I start sql from command line?
What is sql engine in oracle?
How to execute OS(operating system) command from pl/sql?
How to Declare Fixed Length String Value In PL SQL
What is an index and types of indexes. How many number of indexes can be used per table ?
12 Answers Accenture, BirlaSoft, Cognizant, CTS, Symphony,
What is self-join and what is the requirement of self-join?
How do you optimize a query?
Why stored procedures are faster than query?
How can I delete duplicate rows?
Why is sql important?
what is organisational index?
How many types of normalization are there?