Post reply

How can I pull first line of each group?

  • May 27, 2004 at 3:29 pm

    #108319

    Hi, All

    Here is my dilema..

    I want to pull each ID for lastest trans_date.

    Data:

    ID         TRANS_DATE        AMOUNT
    1             10/12/2002          500.00
    2             07/08/2001          250.00
    3              08/25/2003         300.00
    2             11/10/2002          450.00
    1             01/26/2003           100.00
    2             03/16/2002           350.00
     
    Any Idea? No Cursor Please!.. When I deal with 10 mil records...No Cursor...
    Thx
    Jay
     
     
     

     

  • May 27, 2004 at 3:48 pm

    #508086

    Try

    SELECT

    N.[ID], N.TRANS_DATE, N.AMOUNT

    FROM

    tblName N

    INNER JOIN

    (SELECT [ID], MAX(TRANS_DATE) TT FROM tblName T2 GROUP BY [ID]) TQ

    ON

    N.[ID] = TQ.[ID] AND

    N.TRANS_DATE = TQ.TRANS_DATE

  • May 27, 2004 at 3:58 pm

    #508087

    Thx..

    Jay

  • May 28, 2004 at 2:44 am

    #508136

    Hi Jay,

    I often heard inner join is to be preferred always. But there is another way to solve your problem using a simple where clause. This is easyer to write down and estimatet execution plan shows no difference in performance. I would like to know if you get the same result using this query:

     

    select * from tablename a where a.trans_date =

    (select max (trans_date) from tablename where id = a.id)

     

    Karsten

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply