Minimum within select query 1

[diwin]

I have a query that finds all donation records where the GroupID <> 0. I get 40 Records. 2 of them have GroupID = 1, 5 of them have GroupID = 3, yet 2 others are in Group 4, etc.

How do I modify the query to get the minimum donationID for Group 1, the minimum donationID for group 2, etc..?

SELECT tblDonations.donationID, tblDonations.donationAmount, tblDonations.donationGroupID, tblDonorContacts.contactID
FROM tblDonorContacts INNER JOIN tblDonations ON tblDonorContacts.contactID = tblDonations.donation_FcontactID
WHERE (((tblDonations.donationGroupID)<>0))
ORDER BY tblDonations.donationID DESC;

This is what I currently get...

donationID donationGroupID contactID
614 1 578
613 1 577
970 2 486
971 2 1382
1831 3 683
1832 3 686
1833 3 772
1834 3 687
1835 3 818
2032 4 1860
2033 4 1859
2213 5 686
2214 5 687
2215 5 772
2216 5 773
2212 5 683
2257 6 889
2258 6 341

This is what I want...

donationID donationGroupID contactID
613 1 577
970 2 486
1831 3 683
2032 4 1860
2212 5 683
2257 6 889


Daniel Dillon
o (<--- brain shown at actual size.)

 

[PHV]

Something like this ?
SELECT D.donationID, D.donationGroupID, D.donation_FcontactID As contactID
FROM tblDonations As D INNER JOIN (
SELECT Min(donationID) As MinID, donationGroupID FROM tblDonations
WHERE donationGroupID <> 0 GROUP BY donationGroupID
) As M ON D.donationID = M.MinID AND D.donationGroupID = M.donationGroupID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CosmoKramer]

Something like:

SELECT tblDonations.donationID, tblDonations.donationAmount, tblDonations.donationGroupID, Min(tblDonorContacts.contactID) as MinofcontactID
FROM tblDonorContacts INNER JOIN tblDonations ON tblDonorContacts.contactID = tblDonations.donation_FcontactID
WHERE (((tblDonations.donationGroupID)<>0))
GROUP BY tblDonations.donationID;

Si hoc legere scis, nimis eruditionis habes

 

[diwin]

This is the one that worked...

SELECT D.donationID, D.donationGroupID, D.donation_FcontactID AS contactID
FROM tblDonations AS D INNER JOIN [SELECT Min(donationID) As MinID, donationGroupID FROM tblDonations
WHERE donationGroupID <> 0 GROUP BY donationGroupID]. AS M ON (D.donationGroupID = M.donationGroupID) AND (D.donationID = M.MinID);

OK. Similar question, only going the other way.

I am using the same donations table with the donationGroupID field, as illustrated above.

What I want to do is described here...
thread701-1202755

Daniel Dillon
o (<--- brain shown at actual size.)