Selecting most recent of dupe transactions

[tsiegle]

What I am trying to do seems so simple, I am not sure why I am having trouble. I am trying to get the Highest transaction amount for each of our customers, however many times they give the same amount every month so in that case I need to find their single highest transaction with the latest transaction date. I have the following sql which pulls the customers highest transactions, but if there are multiple, how do I say only give me the latest transaction date?

SELECT dt3.donorid, dt3.trandate, dt3.tranamount, dt3.transourcecode
FROM (SELECT DT.trandate, DT.tranamount, DT.donorid, DT.transourcecode
FROM donortrans dt, donor d
WHERE tranamount = (
Select Top 1 max(tranamount)
From donorTrans dt2
where dt2.tranamount>0 and
dt2.donorid =dt.donorid)
and dt.donorid=d.Donorid
) as DT3, donor d
WHERE DT3.DonorID = d.Donorid and d.donorid=10058
ORDER BY DT3.Donorid

Thansk so much for your help!

 

[JarlH]

A GROUP BY will do it:

SELECT dt3.donorid, MAX(dt3.trandate), dt3.tranamount, dt3.transourcecode
FROM (SELECT DT.trandate, DT.tranamount, DT.donorid,
DT.transourcecode
FROM donortrans dt, donor d
WHERE tranamount = (
Select Top 1 max(tranamount)
From donorTrans dt2
where dt2.tranamount>0 and
dt2.donorid =dt.donorid)
and dt.donorid=d.Donorid
) as DT3, donor d
WHERE DT3.DonorID = d.Donorid and d.donorid=10058
GROUP BY dt3.donorid,dt3.tranamount,dt3.transourcecode
ORDER BY DT3.Donorid


Besides, why do you specify TOP 1 MAX()?
MAX does only return one single value, so TOP 1 is redundant.

 

[tsiegle]

Thank you, but I already tried that. If I do that it also groups on TranSourceCode which is usually different for each transaction so they both come back, not just the most recent. This results in pulling these two records:

donorid trandate tranamount transourcecode
----------- ------------------------------------------------------ --------------------- --------------
10058 2000-12-21 00:00:00.000 180.0000 D1100NAA
10058 1999-12-20 00:00:00.000 180.0000 D1199CAB

I only want the first one, the most recent date, but I still need to return all these fields.

Thanks!

 

[JarlH]

Add something like:

and dt3.trandate = (select max(trandate)
from donortrans as dt4
where dt3.donorid = dt4.donorid)


/Jarl

 

[hnkadmn]

You can use the parameter
FETCH FIRST 1 ROW ONLY and
remove the group by and order by.

This will work only in DB2..If it is another, search for similar statement.