Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Question Unanswered: How to display the first line of each group

    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since there is no sequence to rows in a table, getting the "first" row will not be possible, sorry

    you could, however, get the row with the smallest emp_id in the group, or the row with the largest sss_no in the group, or something similar

    but it has to be based on the values in some column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    8

    Red face

    hmm....ok getting the smallest emp_id or largest sss_no w/ the active_date as the base...how will I able to display it? What will be the query?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.emp_id
         , t.sss_no
         , t.dept
      FROM ( SELECT dept
                  , MIN(emp_id) min_emp_id
               FROM emp_list
             GROUP
                 BY dept ) m
    INNER
      JOIN emp_list t
        ON t.dept = m.dept
       AND t.emp_id = m.min_emp_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, perhaps,
    Code:
    select e.emp_id, e.sss_no, e.dept
    from emp_list e
    where e.emp_id = (select min(e1.emp_id) 
                      from emp_list e1
                      where e1.dept = e.dept
                     );
    Though, what would
    w/ the active_date as the base
    be? What "active_date"?
    Last edited by Littlefoot; 07-21-09 at 02:15.

  6. #6
    Join Date
    Jul 2009
    Posts
    8
    Active_date will be another column of the table.

    both of your queries works

    Thanx a lot guys.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by alvingo
    both of your queries works
    but the join to the derived table (my solution) typically outperforms the correlated subquery solution

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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