Select most recent transaction within a subquery

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select most recent transaction within a subquery

Author

Topic

tsiegle
Starting Member

9 Posts

Posted - 2003-11-04 : 15:17:13

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

Thanks so much for your help!

Thanks!

Tracy Siegle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-04 : 15:18:10

Use the MAX function.

MAX(dt3.trandate)

Tara

tsiegle
Starting Member

9 Posts

Posted - 2003-11-04 : 15:24:55

Thanks Tara, I tried that but it won't allow me to do that because even if I Group BY the DonorID because the other two fields are not contained in either an aggregate function or the GROUP BY clause.

An example of the results I have are below - I can't group on the sourcecode or transaction amount because then it would return both of them....

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

Thanks!

Tracy Siegle

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 15:27:42

How would you decide which one you want?

YOu can wrap MAX around thos as well...just make sure you sort for "predicatable" results...you understand, that the data belongs to the grouping...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-04 : 15:29:19

This will get you the dates:

SELECT MAX(dt3.trandate)
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.trandate
ORDER BY DT3.Donorid

You'll then have to JOIN on the above table to get your other columns to display. If you provide DDL for tables, DML for sample data, and expected result set using sample data, we could come up with the solution.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources