1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to display the first line of each group

Discussion in 'SQL PL/SQL' started by alvingo, Jul 20, 2009.

  1. alvingo

    alvingo Guest

    Hi Guys,

    Need help on what will be the sql squery that can display the desired output?

    Table name: emp_list

    Output:
    emp_id sss_no dept
    3451 131567 tig
    2342 221899 tig
    9832 332110 tig
    7812 253456 hr
    3498 331234 hr
    4562 112119 utl
    5621 455663 utl
    .... etc.


    Desired Output: (displaying only the first line for each dept)
    emp_id sss_no dept
    3451 131567 tig
    7812 253456 hr
    4562 112119 utl
    .... etc.

    Pls. help :)

    Thanx
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hmmm you do not have any id on which the groups can be qualified. So it would not be possible to get the first row of each group. You can however get the row with minimum or maximum emp_id or sss_no per group by using a self-join.
     
  3. orablogger

    orablogger Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Utah, US
    Hello there alvingo. It has been a while since you posted this, but I thought I should put in something here for other people that are looking for similar functionality.

    This is a way I think this can be done, by using Oracle's analytic functions.

    Using Oracle's standard SCOTT schema. Consider this query:

    Code (SQL):
    SQL> SELECT e.empno
      2         ,e.deptno
      3          ,e.hiredate
      4  FROM   emp e
      5  ORDER BY e.deptno ,e.hiredate
      6  /
     
    EMPNO DEPTNO HIREDATE
    ----- ------ -----------
     7782     10 6/9/1981
     7839     10 11/17/1981
     7934     10 1/23/1982
     7369     20 12/17/1980
     7566     20 4/2/1981
     7902     20 12/3/1981
     7788     20 4/19/1987
     7876     20 5/23/1987
     7499     30 2/20/1981
     7521     30 2/22/1981
     7698     30 5/1/1981
     7844     30 9/8/1981
     7654     30 9/28/1981
     7900     30 12/3/1981
     
    14 ROWS selected
     
    Let's say we want to return the first hire for each department. This is what can be done:
    Code (SQL):
    SQL> SELECT x.empno
      2         ,x.deptno
      3          ,x.hiredate
      4  FROM   (
      5              SELECT e.empno
      6                      ,e.deptno
      7                      ,e.hiredate
      8                      ,rank() OVER (partition BY e.deptno ORDER BY e.hiredate) hire_date_rank
      9              FROM   emp e
     10              ORDER BY e.deptno
     11         ) x
     12  WHERE  x.hire_date_rank = 1
     13  /
     
    EMPNO DEPTNO HIREDATE
    ----- ------ -----------
     7782     10 6/9/1981
     7369     20 12/17/1980
     7499     30 2/20/1981
     
    Hope this was a good help for someone.

    ~ TJ
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    @ alvingo.

    There no such thing like First or last in an RDBMS like Oracle.
    Mr Tom says it here
     
  5. orablogger

    orablogger Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Utah, US
    Rajavu,

    I use these analytic functions in Oracle all the time as a Data Warehouse Engineer. It is a necessity in my work.

    The article you are referring to is from 2001...

    Read the Oracle SQL Reference and you will find answers. ;)

    I actually started writing about this on my blog post How To Use Oracle's Analytic Functions @ my website orablogger(dot).net. You will also find links to Oracle's documentation there.

    ~ TJ
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Orablogger,

    I agree with you. You are perfectly right when you have date field or something to get the first and last employee in a field. But when you have a table emp_list with fields emp_id, sss_no and dept you cant get the first inserted or last inserted raw. Even if you find a solution it will not be consistent.

    By the way there were such identical solutions in Oracle in 2001 also for getting the first or last record based on a field. still there. Still the RDBM concept stands good for oracle like any other RDBMS.