How to select greatest of group, w/add'l fields

Posted on 2008-06-12
Last Modified: 2010-03-20
Please see my short question/answer here:

The SQL I was given (with my table names):

SELECT Table1.Num, Max(Table1.Rev) AS MaxRev
FROM Table1
GROUP BY Table1.Num;

worked for just the grouped field and the max revision, but I couldn't add other fields to it.  For example, this:

SELECT Table1.Num, Max(Table1.Rev) AS MaxRev, Table1.Date
FROM Table1
GROUP BY Table1.Num;

is not valid SQL, or at least Access gives me a "you tried to execute a query that does not include the specified expression 'Date' as part of an aggregate function".  When I try to add add'l fields using the Access query designer, it adds them to the GROUP BY clause, as follows:

SELECT Table1.Num, Max(Table1.Rev) AS MaxRev, Table1.Date
FROM Table1
GROUP BY Table1.Num, Table1.Date;

But then it's grouping by date too, and it's selecting the maximum of the wrong groups; i.e., I am returning all of my records rather than the latest revisions.

Does anyone have any SQL to return ALL fields grouped by one field (in my case, text field 'Num') and with the maximum of each group of another field (in my case, numeric field 'Rev').

(Apparently '*' doesn't work in grouping queries either.  Grr.)

Question by:riceman0
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 21770730

select * from Table1
where  rev in (select max(rev) from Table1 as T1 where T1.num=table1.num)

Author Comment

ID: 21770875

Okay, this is weird.  It worked well on a small table, but I tried to move it to my real (large) table and the grouping gets messed up.  It seems like when I include a particular date field (and it's not even the only dat field!) the grouping gets broken and it returns every record. But when I exclude that date field, it works.

Can anyone think of ANY reason why this might be...
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21771013
what are you talking about?

Author Comment

ID: 21771056
I'm saying your approach doesn't work on my real table.  The 3-field table design I gave in the question was of course reducing the problem to a bare minimum.  I am looking into this and will provide more info as I find it.  I wanted to provide my initial (weird) observation (that the inclusion of this date field would make it not work) on the off chance it rang a bell with someone.

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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