How do I get the latest row value?
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 21st, 2010 7:22pm
Hello, You can do an OUTER APPLY on your sql query and get the TOP 1 PTID ORDER BY PTID DESC. Hope this helps! Ayesha.
October 21st, 2010 7:56pm
You can also use MAX and GROUP BY in your SQL query. OR You can use SSRS MAX Function as Max(Expression, Scope) if you want it to do on SSRS Thanks
October 21st, 2010 9:01pm
Thank you but can i actually have the code if that is possible. Thank you again. Below is the query I am using SELECT acct_no as 'Account No', acct_type as 'Account Type', effective_dt as 'Effective Date', reversal_dt as 'Reversal Date', tran_code as 'Transaction Code', rownumber as 'Row No', currentbalance -amt as 'Ending/Current Balance', amt as 'Transaction Amt', currentbalance as 'Prior Balance', ptid FROM final where effective_dt ='6/30/2010'
October 21st, 2010 9:26pm
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 This works. Go it
October 21st, 2010 10:06pm