SQL to select greatest of group?

Hi, I have a table where the primary keys are DocumentID and RevisionNumber.  Is there a way to formulate a query in SQL such that it selects all the latest revisions; i.e., the highest revision number of each document group?

Thanks.
riceman0Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
SELECT [Document Group], Max([Rev Number]) As MaxRev
FROM YourTableName
GROUP By [Document Group]

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
select t.*
  from yourtable t
 where t.revisionnumber = DMAX("RevisionNumber", "yourtable", "DocumentID= " & t.DocumentId)

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Using your field names:

SELECT [Document Group], Max([RevisionNumber]) As MaxRevNumber
FROM YourTableName
GROUP By [Document Group]
0
All Courses

From novice to tech pro — start learning today.