[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-06-12
4
Medium Priority
?
209 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

612 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