Selecting Last Transaction

[sap1958]

begin try drop table #Last end try begin catch end catch

select giftid,giftjntamt,gifteffdat,ROW_NUMBER()
OVER(PARTITION BY giftid ORDER BY gifteffdat)as col
into #Last
from gifts with(nolock)
where gifteffdat >='7/1/1984'
and giftacctdv = '11'
and gifttype in ('c','g','y')
group by giftid,giftjntamt,gifteffdat
order by giftid

sample data
giftid giftjntamt gifteffdat col
0000000014 500.00 2004-06-02 00:00:00.000 1
0000000014 2000.00 2005-08-09 00:00:00.000 2
0000000014 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 1
0000000270 10.00 2008-02-22 00:00:00.000 2(desired)
Desired output
0000000014 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

 

[bborissov]

SELECT *
FROM (
select giftid,
       giftjntamt,
       gifteffdat,
       ROW_NUMBER() OVER (PARTITION BY giftid 
                                   ORDER BY gifteffdat DESC) as col
from gifts with (nolock)
where gifteffdat >='7/1/1984'
  and giftacctdv = '11'
  and gifttype in ('c','g','y')
) Tbl1
WHERE Col = 1
order by giftid

(not tested)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.