Solved

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

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

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23474706.html

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.)

Thanks.
0
Comment
Question by:riceman0
  • 2
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility

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

Author Comment

by:riceman0
Comment Utility

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...
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
what are you talking about?
0
 

Author Comment

by:riceman0
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now