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?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
Using your field names:

SELECT [Document Group], Max([RevisionNumber]) As MaxRevNumber
FROM YourTableName
GROUP By [Document Group]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.