record with largest value in a specified field

[BURL ives]

OK OK......stuck again.....looking for more help.........

without explaining the whole database, I hope I can explain the
situation by the example below..........

I have a query that pulls all the records from a table. For example,
the query outputs the following:

TITLE REV POSITION DATE
methodA / 0 / tech / july 5
methodA / 1 / tech / july 10
methodB / 0 / tech / july 5
methodB / 1 / tech / july 10
methodB / 2 / tech / july 12
methodC / 0 / tech / july 6
methodC / 1 / tech / july 9
methodC / 2 / tech / july 20
methodD / 0 / tech / july 5

I'd like the output to be:

TITLE REV POSITION DATE
methodA / 1 / tech / july 10
methodB / 2 / tech / july 12
methodC / 2 / tech / july 20
methodD / 0 / tech / july 5


Basically for a given method, I want to get the record containing the
largest value of REV. How does one get only those records that
contain the largest value from that specific field?

I tried MAX and GROUP BY but they didn't seem to work.

thank you in advance

 

[John Spencer]

The SQL for that would look something like the following

SELECT YourTable.Title
, YourTable.Rev
, YourTable.Position
, YourTable.[Date]
FROM YourTable INNER JOIN
(SELECT Title, Max(Rev) as MRev
FROM YourTable
GROUP BY Title) as T
ON YourTable.Title = T.Title And
YourTable.Rev = T.MRev
ORDER BY Title



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================