RE: display deptno wise first three lowest salaries in ascending order of deptno and desc order of salary
SELECT ename, sal,deptno FROM (SELECT ename, sal,deptno, rank() over(PARTITION BY deptno ORDER BY sal ) low_3 FROM emp) WHERE low_3 <=3 ORDER BY deptno , sal DESC;
RE: display deptno wise first three lowest salaries in ascending order of deptno and desc order of salary
[CODE] SQL> SELECT EMPNO,ENAME,JOB,DEPTNO,SAL,SAL_ORD 2 FROM 3 ( 4 SELECT EMPNO,ENAME,JOB,DEPTNO,SAL, 5 RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) SAL_ORD 6 FROM EMP 7 ) 8 WHERE SAL_ORD <=3 9 ORDER BY DEPTNO;
EMPNO ENAME JOB DEPTNO SAL SAL_ORD --------- ---------- --------- --------- --------- --------- 7934 MILLER CLERK 10 1730.3 1 7782 CLARK MANAGER 10 3260.95 2 7839 KING PRESIDENT 10 6655 3 7876 ADAMS CLERK 20 1464.1 1 7566 JONES MANAGER 20 3959.73 2 7788 SCOTT ANALYST 20 3993 3 7902 FORD ANALYST 20 3993 3 7900 JAMES CLERK 30 1264.45 1 7521 WARD SALESMAN 30 1663.75 2 7654 MARTIN SALESMAN 30 1663.75 2 7456 KRISHNA 10000 1
RE: display deptno wise first three lowest salaries in ascending order of deptno and desc order of salary
select e.* from ( select enmae,sal, dense_rank() over(partition by deptno order by sal) ranksal from emp)e where e.ranksal<=3 we can use rank fn also but rank fn skips the rank but never dense_rank() skips rank so better solution is dense_rank()
