Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: Select first and last row data for each group in SQL 2000

    Hi,

    Can anybody help me how to do a query to select first and last row data for each date and time?
    For example here is my data in table CustomerPayment

    cus_id pay_date pay_time pay_amount
    201001 27/01/2010 235959 500.00
    201001 27/01/2010 235945 200.00
    201001 27/01/2010 191512 200.00
    201051 27/01/2010 235945 900.00
    201001 29/01/2010 235945 200.00
    201048 29/01/2010 191351 450.00
    201048 29/01/2010 191512 450.00
    201048 29/01/2010 235945 200.00
    201048 29/01/2010 235945 180.00
    201048 29/01/2010 235945 100.00
    201048 30/01/2010 191512 100.00
    201001 30/01/2010 235945 180.00

    I want to select first and last row group by cus_id for each pay_date.
    If only 1 row perdate then i need to take it also.

    So the result should be like this

    cus_id pay_date pay_time pay_amount
    201001 27/01/2010 235959 500.00
    201001 27/01/2010 191512 200.00
    201051 27/01/2010 235945 900.00
    201001 29/01/2010 235945 200.00
    201048 29/01/2010 191351 450.00
    201048 29/01/2010 235945 100.00
    201048 30/01/2010 191512 100.00
    201001 30/01/2010 235945 180.00

    Is it possible to do it in SQL 2000?
    Please help me
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    What I would do
    1st query select the first
    2rd query select the last
    then UNION them
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    Hi Myle,

    Thanks. But could you give a details on how to select the first and the last row?As i just dont have any field that can use as min and max because some of them got same value.

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aina View Post
    ...i just dont have any field that can use as min and max because some of them got same value.
    that might be a problem, you decide...

    this is untested --
    Code:
    WITH grouped_payments 
    AS ( 
    SELECT cus_id 
         , pay_date 
         , pay_time 
         , pay_amount
         , ROW_NUMBER() OVER 
              ( GROUP BY pay_date, cus_id
                ORDER BY pay_time) AS row_num
      FROM CustomerPayment
    )
    , max_row_payments
    AS ( 
    SELECT cus_id 
         , pay_date 
         , 1            AS min_row
         , MAX(row_num) AS max_row 
      FROM grouped_payments
    GROUP
        BY cus_id 
         , pay_date 
    )
    SELECT t.cus_id 
         , t.pay_date 
         , t.pay_time 
         , t.pay_amount
      FROM max_row_payments AS m
    INNER
      JOIN grouped_payments AS t
        ON t.cus_id   = m.cus_id   
       AND t.pay_date = m.pay_date 
     WHERE t.row_num IN ( m.min_row, m.max_row )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    Select *,
    (select top 1 t2.date from TAB1 T2 where t2.cust_id=t1.cust_id order by T2.date asc) as First,
    (select top 1 t3.date from TAB1 T3 where t3.cust_id=t1.cust_id order by T3.date desc) as Last
    from TAB1 T1

Posting Permissions

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