TOP 1 From GroupBy Query Issue

[Techno_Dex]

Unfortunately I have been away from advanced SQL queries for to long and
have lost some of my knowledge. I'm hoping someone might be able to help me
recover this knowledge. I have a .NET 2.0 App using the Enterprise Library
(Connection String used Jet DB Engine). I have a table that I would like to
do a group by on two fields, then extract the top 1 record from each group
(using an order by clause to get the desire record to top the top of each
group). I have racked my brain on INNER JOINs, OUTER JOINs, DISTINCTS,
MAX() but can't come up with the solution.

What I have is a Table "XDocs" which has a Primary Key of ID, a field called
GroupID and a field called Version (other fields are in the table also)

ID GroupID Version Name
--- ---------- --------- --------
1 XA1 0 Document1
2 XA1 1 Document1
3 XA2 0 Document2
4 XA2 1 Document2
5 XA2 2 Document2
6 XA3 0 Document3
7 XA3 1 Document3
8 XA3 2 Document3
9 XA4 0 Document4

Results I would like are below. I want to group by GroupID and Version
ORDER BY GroupID, Version DESC (i.e.
SELECT GroupID, Version FROM XDocs GROUP BY GroupID, Version)

ID GroupID Version Name
--- ---------- --------- --------
2 XA1 1 Document1
5 XA2 2 Document2
8 XA3 2 Document3
9 XA4 0 Document4

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Techno_Dex]

Roger
Maybe I'm missing something here, but which example in your TopQuery.mdb
database are you suggesting I use? Example 4 "Advanced" Solution 1 and
Solution 2 look like the closest possible examples but don't appear to get
the data I am trying to extract. The GroupID and Version values in my
example can be combined to be a secondary key but alone can not be used to
extract the same record from the table aliased as v2 (i.e. joining the table
to itself). This knocks out the Solution 1 which uses the IN clause because
that only matches on one field where I need to match on two (GroupID and
Version). Solution 2 with the HAVING clause appeared to have some potential
but again is only joining on one field at a time instead of both GroupID and
Version. I can't use the ID column as an aggregate function like MAX() or
DISTINCT will require a GROUP BY clause which then the ID column makes every
record a DISTINCT Record. If I am missing something here, please enlighten
me. I know SQL can be twisted in an infinite number of ways to get the same
results and maybe I am looking at this wrong.

TIA

 

[Roger Carlson]

Sorry, I read the question too quickly. A better example would have been my
sample "MaxQueryProblem.mdb".

I'd suggest using two queries.

The first one to find the Max values. Save it as qryMaxDocs:
SELECT GroupID, Max(Version) AS MaxOfVersion
FROM XDocs
GROUP BY GroupID
ORDER BY GroupID;

Then use this query to Join back to the original table to get your addtional
fields:
SELECT ID, GroupID, Version, Name
FROM XDocs INNER JOIN qryMaxDocs ON
(XDocs.Version = qryMaxDocs.MaxOfVersion)
AND (XDocs.GroupID = qryMaxDocs.GroupID);

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Techno_Dex]

I came up with something similar but didn't think it was the correct/elegant
approach. I thought there was better functionality to extract the Top
record from each grouping than this. I want to avoid the creation of a
separate query in the database as I am writing this in code. Here is what I
came up with.

SELECT XD.* FROM (
SELECT XD2.GroupID, MAX(XD2.Version) AS Version
FROM XDocs XD2 GROUP BY XD2.GroupID) AS XD2
INNER JOIN XDocs XD ON (XD.GroupID=XD2.GroupID AND XD.Version=XD2.Version);

I need to get the GroupBy values first in order to tie them in a where
clause or inner join to a redefinition of the table. So are you saying
there isn't cleaner functionality to get the top X records from a grouping?

 

[Roger Carlson]

I can't come up with one.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L