Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
omega1983
Starting Member
40 Posts |
 Posted - 2010-08-20 : 10:26:43
|
| begin try drop table #Last end try begin catch end catchselect giftid,giftjntamt,gifteffdat,ROW_NUMBER()OVER(PARTITION BY giftid ORDER BY gifteffdat)as colinto #Lastfrom gifts with(nolock)where gifteffdat >='7/1/1984'and giftacctdv = '11'and gifttype in ('c','g','y')group by giftid,giftjntamt,gifteffdatorder by giftidsample datagiftid giftjntamt gifteffdat col0000000014 500.00 2004-06-02 00:00:00.000 10000000014 2000.00 2005-08-09 00:00:00.000 20000000014 1000.00 2007-04-09 00:00:00.000 3 (desired)0000000081 250.00 2002-03-12 00:00:00.000 1(desired)0000000147 30.00 2006-04-05 00:00:00.000 1(desired)0000000270 1000.00 1995-10-05 00:00:00.000 10000000270 10.00 2008-02-22 00:00:00.000 2(desired)Desired output0000000014 1000.00 2007-04-09 00:00:00.000 0000000081 250.00 2002-03-12 00:00:00.000 0000000147 30.00 2006-04-05 00:00:00.000 0000000270 10.00 2008-02-22 00:00:00.000 Essentially I want the last row for each giftid because it represents the most recent transaction. Thus I want the last giftid, last giftamount and last giftdate.Using the row feature how would I accomplish this |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-20 : 10:48:35
|
| [code]select giftid,giftjntamt,gifteffdat from(select giftid,giftjntamt,gifteffdat,ROW_NUMBER()OVER(PARTITION BY giftid ORDER BY gifteffdat DESC)as seqfrom giftswhere gifteffdat >= '19840701'and giftacctdv = '11'and gifttype in ('c','g','y')) t where t.seq = 1[/code] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Selecting Last Transaction Using Row() feature