Solved

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

Posted on 2008-06-12
4
194 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 120

Accepted Solution

by:
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)
0
 

Author Comment

by:riceman0
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...
0
 
LVL 120

Expert Comment

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

Author Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 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