My SQL version:-n=3
----------------------
SELECT TOP 1 salary
FROM (
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC ) AS emp
ORDER BY salary ASC;
_____________________________
Oracle version:-n=5
--------------------------
1.
select * from (SELECT *
FROM (select * from emp ORDER BY sal DESC)
WHERE rownum <= 5
ORDER BY rownum) order by sal asc;
2.
select min(sal) from (SELECT *
FROM (select * from emp ORDER BY sal DESC)
WHERE rownum <= 5
ORDER BY rownum)
order by sal asc ;
----------------------
SELECT TOP 1 salary
FROM (
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC ) AS emp
ORDER BY salary ASC;
_____________________________
Oracle version:-n=5
--------------------------
1.
select * from (SELECT *
FROM (select * from emp ORDER BY sal DESC)
WHERE rownum <= 5
ORDER BY rownum) order by sal asc;
2.
select min(sal) from (SELECT *
FROM (select * from emp ORDER BY sal DESC)
WHERE rownum <= 5
ORDER BY rownum)
order by sal asc ;
Above solution is applicable for unique values for salary .
ReplyDeleteFor duplicate values use following query --- ::
select min(sal) from (SELECT *
FROM (select distinct (sal) from emp1 ORDER BY sal DESC)
WHERE rownum <= 5 order by sal desc);
(Y)
DeleteSELECT DISTINCT(sal)
ReplyDeleteFROM EMP a
WHERE (n-1) =(
SELECT COUNT(DISTINCT(b.sal)) FROM EMP b WHERE b.sal > a.sal
);