Results 1 to 4 of 4

Thread: top 3 records

  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: top 3 records

    Hi Experts,

    How to get top 3 records from table.EMP Table field SAL...
    ExAL
    4
    5
    2
    8
    9
    The out put should like
    9
    8
    5
    Could you please any one help me.How to write the sql using orcle..

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quite a few options.

    This is what we have:
    Code:
    SQL> select ename, sal from emp order by sal desc;
    
    ENAME             SAL
    ---------- ----------
    KING             5800
    FORD             3800
    SCOTT            3800
    JONES            3775
    BLAKE            3650
    CLARK            3250
    ALLEN            2400
    TURNER           2300
    MILLER           2100
    WARD             2050
    MARTIN           2050
    ADAMS            1900
    JAMES            1750
    SMITH            1600
    One way is to sort salaries and, using the ROWNUM pseudo column, fetch the result:
    Code:
    SQL> select sal
      2  from (select sal
      3        from emp
      4        order by sal desc
      5       )
      6  where rownum <= 3;
    
           SAL
    ----------
          5800
          3800
          3800
    The same can be done with the RANK analytical function:
    Code:
    SQL> select sal
      2  from (select
      3          sal,
      4          rank() over (order by sal desc) rnk
      5        from emp
      6       )
      7  where rnk <= 3;
    
           SAL
    ----------
          5800
          3800
          3800
    As you can see, the second and the third salary are the same (3800). Jones' salary is 3775, which is - actually - the "third" salary if we take 3800 to be the same. In that case, use DENSE_RANK analytical function:
    Code:
    SQL> select sal
      2  from (select
      3          sal,
      4          dense_rank() over (order by sal desc) drnk
      5        from emp
      6       )
      7  where drnk <= 3;
    
           SAL
    ----------
          5800
          3800
          3800
          3775
    If you need only three values (5800, 3800, 3775), then SELECT DISTINCT.

    There might be other options, but I guess that these are enough to give you an idea of how that can be done. Do some research by yourself.

  3. #3
    Join Date
    Jun 2011
    Posts
    2
    Hi,

    Thank you so much...

  4. #4
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    another way - NTH_VALUE
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •