May 6, 2010 at 2:16 pm
i'm a newbie at sql and i was wondering what query i would use to get the last occurence for each account #. Here is the rows I want:
Acct # Transaction Date Transaction Amt Rowid
1000 01/01/2010 100.00 1
1000 01/02/2010 150.00 2 (Want this)
2000 01/01/2010 100.00 3
2000 01/02/2010 111.00 4 (Want this)
3000.....
May 6, 2010 at 2:25 pm
Figured out. Thread closed
May 6, 2010 at 2:26 pm
kuyaglenn (5/6/2010)
Figured out. Thread closed
If you figured it out, proper forum ediquette would have you post your solution. Others may have a similar problem, and your solution may help.
May 6, 2010 at 2:28 pm
I've always disliked the need to write these sorts of queries because I've never come up with a way that I felt was truly *optimal*.
But in general, this is what I use for these type of things. Note, that I'm only using a temp table as an example:
create table #Accts (
AcctNum int,
TransactionDate datetime,
TransactionAmt money,
Rowid int
)
insert #Accts (AcctNum,TransactionDate,TransactionAmt,Rowid)
values (1000,'1/1/2010',100.00,1)
insert #Accts (AcctNum,TransactionDate,TransactionAmt,Rowid)
values (1000,'1/2/2010',150.00,2)
insert #Accts (AcctNum,TransactionDate,TransactionAmt,Rowid)
values (2000,'1/1/2010',100.00,3)
insert #Accts (AcctNum,TransactionDate,TransactionAmt,Rowid)
values (2000,'1/2/2010',111.00,4)
select a.AcctNum,a.TransactionDate,a.TransactionAmt,a.Rowid
from #Accts a
join (
select a1.AcctNum,max(a1.TransactionDate) as TransactionDate
from #Accts a1
group by a1.AcctNum
) LastOccurrence
on a.AcctNum = LastOccurrence.AcctNum
and a.TransactionDate = LastOccurrence.TransactionDate
drop table #Accts
May 6, 2010 at 2:41 pm
Solution to my problem is this:
SELECT A.ACCT,A.TRANDATE,A.TRANAMOUNT,A.RWID
FROM MYTABLE A
WHERE RWID = (SELECT MAX(B.RWID) FROM MYTABLE B WHERE B.ACCT = A.ACCT)
May 6, 2010 at 2:43 pm
As this is in a SQL Server 2008 forum, another option:
with Latest_Transactions as (
select
row_number() over (partition by AcctNum order by TransactionDate desc) as rownum,
AcctNum,
TransactionDate,
TransactionAmt,
RowId
from
dbo.TransTable
)
select
AcctNum,
TransactionDate,
TransactionAmt,
RowId
from
Latest_Transactions
where
rownum = 1;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply