Solved

SQL to select greatest of group?

Posted on 2008-06-10
3
164 Views
Last Modified: 2010-03-20
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.
0
Comment
Question by:riceman0
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 21757115
SELECT [Document Group], Max([Rev Number]) As MaxRev
FROM YourTableName
GROUP By [Document Group]

0
 
LVL 143

Expert Comment

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

Open in new window

0
 
LVL 75
ID: 21757137
Using your field names:

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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question