Imran_Javed

 Profile Answers by Imran_Javed 

• May 8th, 2008

 

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;

tariq_oracle

 Profile Answers by tariq_oracle 

• May 9th, 2008

 

select * from emp a
where
3>=
(select count(distinct sal) from emp b
where a.sal>=b.sal
and a.deptno=b.deptno)
order by sal desc

krishnaindia2007

 Profile Answers by krishnaindia2007 

• Jun 20th, 2008

 

[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

11 rows selected.
[/CODE]

hiasat

 Profile Answers by hiasat 

• Aug 4th, 2008

 

SELECT empno,ename,sal
FROM (
select empno,ename,sal
from emp
order by sal asc
)
WHERE rownum <=3

EMPNO SAL
--------- ---------
7369 800
7900 950
7876 1100

enjoy!! by HIASAT

vishnu vardhan reddy

 Profile Answers by vishnu vardhan reddy 

• Sep 16th, 2008

 

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()