October 18, 2004 at 9:32 am
Would some guru like to suggest the most efficient way to get the following info:
I have a transactions table. Pertinent fields are TransID, MemberID, DateOfTrans, Amount, TransType
I want to get the date and amount of the Maximum contribution ever from each member (each unique MemberID).
Thanks.
October 18, 2004 at 11:33 am
This will select maximum transaction amounts, including ties:
SELECT TransID, MemberID, DateOfTrans, Amount, TransType
FROM Transactions
WHERE Amount =
(SELECT MAX(Amount)
FROM Transactions T1
WHERE T1.MemberId = Transactions.MemberId)
--
Adam Machanic
whoisactive
October 18, 2004 at 12:24 pm
Thanks so much.
October 18, 2004 at 12:34 pm
The query is returning multiple records per customer. If I want to get only the most recent dated transaction for each customer if there are more than one record, how would I modify the query?
October 18, 2004 at 3:54 pm
SELECT C.TransID, C.MemberID, C.DateOfTrans, C.Amount, C.TransType
FROM ( SELECT DISTINCT MemberID FROM Transactions ) A
LEFT JOIN Transactions B
ON B.Amount = ( SELECT MAX(Amount) FROM Transactions B2 WHERE B2.MemberId = A.MemberId )
AND B.MemberId = A.MemberId
LEFT JOIN Transactions C
ON C.TransID = ( SELECT MIN(TransID) FROM Transactions C2 WHERE C2.MemberId = A.MemberId AND C2.Amount = B.Amount )
AND C.MemberId = A.MemberId
WHERE C.TransId = B.TransId /* Gets rid of duplicates introduced by the LEFT JOIN B */
October 18, 2004 at 4:01 pm
As for optimization, I think no matter what SQL you eventually use you will want to make sure there is an index for the MemberId column probably all by itself. Perhaps with the Amount column in it as well.
If the distinct MemberIDs is small and the number of transactions is very large, you may want to change the SELECT DISTINCT derived table to a query on a table that lists every defined MemberId. You would then need to add a WHERE clause such that the A.MemberId IS NOT NULL to prevent reporting all NULLs for MemberIds that are defined by have no transactions.
Perhaps someone else can think of a leaner version of the SQL I provided. Best of luck.
October 18, 2004 at 8:38 pm
SELECT TransID, MemberID, DateOfTrans, Amount, TransType
FROM Transactions
WHERE TransID = (
SELECT TOP 1 TransID FROM Transactions T1
WHERE T1.MemberId = Transactions.MemberId
ORDER BY Amount DESC, DateOfTrans DESC
)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply