Help: sorting within GROUP BY

I'm having difficulty reducing a dataset in Access using what I
thought were standard SQL grouping and sorting technique. Someone set
me straight, please? Seems like ORDER BY and GROUP BY are not applied
in the hierarchy I thought.

I'd like to GROUP BY a repetitive element, and pick the associated
elements by sorting one of them (alphabetically, ideally), then
choosing FIRST of the associated element.

An example, tblPints:
BrandID BeerType
LocalBrew IPA
LocalBrew porter
LocalBrew stout
LocalBrew heif
WaBrew porter
WaBrew stout
WaBrew Hefeweizen

The query:
SELECT tblPints.BrandID, First(tblPints.BeerType) AS FirstOfBeerType
FROM tblPints
GROUP BY tblPints.BrandID
ORDER BY First(tblPints.BeerType);

I expected to choose:
LocalBrew IPA
WaBrew Hefeweizen
....since IPA and heif should sort to the top alphabetically, however
my result was:

BrandID FirstOfBeerType
LocalBrew IPA
WaBrew porter

(Which is ok, since I prefer a porter, but...)

Is there a way to sort these so I get what I anticipated? Thanks for
your help.

One problem I am seeing is that in the LocalBrew group -- heif comes
before IPA. Another issue is that the First function in Jet Sql will
select the first record entered for a given group. You can't order the
rows and select the first row in your custom order. First will pick the
first record entered no matter what order.

One workaround would be to create some temp tables for each of the
groups and then within these temp tables add a column that you would
sort by where you could say IPA has a letter 'a' in columnSort, heif
has a letter 'b', ... then you select the top 1 row from each temp table
where you order by columnSort.

If you need to accomplish this in one step you will have to step up to
Transact sql (Tsql for sql server). Jet sql is a sort of subset of Tsql
with a few Jet sql functions like First and Last which only exist in Jet
Sql. A lot of Tsql syntax can be used in Access - probably half of
Tsql. Tsql could handle your request in one step, but that syntax won't
work in Access. I have tried, and no one has come up with a workaround
for it. Here is the Tsql Version (note: this requires an Identity
column (autonum column) which I named rowID in this sample)

SELECT * FROM tblPints t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 1 * FROM tblPints t2 WHERE t2.BrandId =
t1.BrandId Order By t1.BeerType) t3 WHERE t3.rowID = t1.rowID)

The only caveat here is that it will bring up heif instead of IPA for
the localBrew group - unless you add a sort column to specify how to
sort your beers.



Rich

*** Sent via Developersdex http://www.developersdex.com ***

Jeff <jnorville@gmail.com> wrote in
news:5f742969-8ca1-4f53-99ac-0e23a9603f78@m7g2000prd.googlegroups.com
:

> I'm having difficulty reducing a dataset in Access using what I
> thought were standard SQL grouping and sorting technique. Someone
> set me straight, please? Seems like ORDER BY and GROUP BY are not
> applied in the hierarchy I thought.
>
> I'd like to GROUP BY a repetitive element, and pick the associated
> elements by sorting one of them (alphabetically, ideally), then
> choosing FIRST of the associated element.
>
> An example, tblPints:
> BrandID BeerType
> LocalBrew IPA
> LocalBrew porter
> LocalBrew stout
> LocalBrew heif
> WaBrew porter
> WaBrew stout
> WaBrew Hefeweizen
>
> The query:
> SELECT tblPints.BrandID, First(tblPints.BeerType) AS
> FirstOfBeerType FROM tblPints
> GROUP BY tblPints.BrandID
> ORDER BY First(tblPints.BeerType);
>
> I expected to choose:
> LocalBrew IPA
> WaBrew Hefeweizen
> ...since IPA and heif should sort to the top alphabetically,
> however my result was:
>
> BrandID FirstOfBeerType
> LocalBrew IPA
> WaBrew porter
>
> (Which is ok, since I prefer a porter, but...)
>
> Is there a way to sort these so I get what I anticipated? Thanks
> for your help.

Yes, base your Totals query on a SELECT query that sorts the rows
alphabetically.
so Query1:
SELECT BrandID,BeerType from tblPints ORDER by BrandID,BeerType;
and query2
SELECT query1.BrandID, First(query1.BeerType) AS FirstOfBeerType
FROM query1
GROUP BY query1.BrandID;



--
Bob Quintal

PA is y I've altered my email address.