Solved

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

830 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