Post reply

How do I get the latest row for each unique account?

  • October 21, 2010 at 12:38 pm

    #230116

    Hi,

    I am pulling the following rows from a resultset, as you see that Acct no - 12345 repeats but has increasing PTID which are distinct. I need to get the row that has the highest PTID for acct no 12345 i.e. ptid 13310125. How do I do that? Please help. Below is the result of my query

    Acct No Acct Type Effective Date Row no Cur Bal Transaction Prior Bal PTID

    12345 CK 6/30/2010 0:00 10 $101,200.40 $3,000.00 $104,200.40 13301633

    12345 CK 6/30/2010 0:00 11 $101,331.78 ($131.38) $101,200.40 13310125

    54321 CK 6/30/2010 0:00 7 $151,292.94 ($292.20) $151,000.74 13310504

    I want to see my result as following

    Acct No Acct Type Effective Date Row no Cur Bal Transaction Prior Bal PTID

    12345 CK 6/30/2010 0:00 11 $101,331.78 ($131.38) $101,200.40 13310125

    54321 CK 6/30/2010 0:00 7 $151,292.94 ($292.20) $151,000.74 13310504

  • October 21, 2010 at 12:47 pm

    #1240519

    SELECT *

    FROM@Table t1

    INNER JOIN (

    SELECT MAX(PTID) as LastPTID,

    AcctNo

    FROM@Table

    GROUP BY AcctNo

    ) t2 ON t1.AcctNo = t2.AcctNo AND t1.PTID = t2.LastPTID

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • October 21, 2010 at 12:58 pm

    #1240526

    Thank you. Works like a charm.

  • October 21, 2010 at 1:02 pm

    #1240530

    No problem.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

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

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