This works (Access again) - SELECT Table1.nonUniqueID, First(Table1.ID) AS FirstOfID, Max(Table1.ValueA) AS MaxOfValueA, First(Table1.ValueB) AS FirstOfValueB, First(Table1.ValueC) AS FirstOfValueC FROM Table1 GROUP BY Table1.nonUniqueID;
--- I have snipped the rest of the correspondence as it's getting long) --- Ian Thomas GeoSciSoft - Perth, Australian -----Original Message----- From: Robert Crossley [mailto:[EMAIL PROTECTED] Sent: Thursday, 13 May 2004 1:38 PM To: SCISOFT Cc: MapInfo List Subject: Re: MI-L Select Highest Value only in Table - Multiple Records (with same ID) Ian, Herein lies Susan's problem, as you suggest, it is rlatively easy to construct a query that will give you the maximum value of Value A for each ID, but it will not point to the record in that table that has that value to get the values in the rest of the fields. Susan asked what are the values for ValueB and ValueC for the record that has that maximum value of ValueA for each ID. Perhaps somehting like would in theory give the result: select * from table Order by ID, ValueA Desc Group By ID Into OrderedList Select ID, First(ValueA), First(ValueB), First(ValueC) from Orderlist Group By ID But I have witnessed some strange results in SQL queries lately that I would not like to trust this approach too much. I certainly would not trust the results of this unless you saved the first query off as a table first. r --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 11730
