this sounds like it should do it for you, but perhaps I'm missing something
SELECT memberID, max(paidThru) AS paidThru FROM someTableName ORDER BY memberID cheers, barneyb On Thu, 17 Mar 2005 22:03:03 -0500, Mark Leder <[EMAIL PROTECTED]> wrote: > I have a table with two columns, memberID (integer) and paidThru (date). I'm > using SQL 2000. > Most rows have only one member ID associated with one paidThru date. > Problem is, some of the records have one memberID which can be associated > with two or more paidThru dates (the multiple dates per member ID would not > be duplicates (not be the same date). > > What I want to do is return the most recent date only and its associated > memberID for all records. > I'm stumped on how to do this correctly. I've tried distinct, group by, and > max without success. > > <cfquery name="getTransactions" datasource="myDataSource"> > SELECT memberID, paidThru > FROM someTableName > ORDER BY memberID asc > </cfquery> > > Thanks in advance. > > Mark > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199282 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
