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.
Free Windows Admin Tool Kit Click here and download it now
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'
Free Windows Admin Tool Kit Click here and download it now
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