Grouping Within A Query

  • Thread starter Thread starter Xcelsoft
  • Start date Start date
X

Xcelsoft

I have discovered an unsual problem with a
grouping/totaling query. I am running Access 2000 and
have used this many times before but for some reason I am
getting strange results. To make it easy to explain I
will provide an example below.

Table1:
NAME COLOR
Emp1 Silver
Emp1 Yellow
Emp1 Gold
Emp2 Red
Emp2 Green
Emp2 Blue

Query1:
Basically selects the Name and Color field from Table1,
grouped by name and selects First Color field, ordered by
Name and Color. See below:

SELECT Table1.NAME, First(Table1.COLOR) AS FirstOfCOLOR
FROM Table1
GROUP BY Table1.NAME
ORDER BY Table1.NAME, First(Table1.COLOR);

Even though the records are sorted by Name then Color,
Ascending, the results are Emp1 Silver and Emp2 Red. The
results should be Emp1 Gold and Emp2 Blue.

If I use the Min option in the query instead of the first
option as shown below the correct results are returned.
Can anyone explain why the First (instead of Min) does
not work.

SELECT Table1.NAME, Min(Table1.COLOR) AS MinOfCOLOR
FROM Table1
GROUP BY Table1.NAME
ORDER BY Table1.NAME, Min(Table1.COLOR);

Thanks

Xcelsoft
 
HI,


First has only the meaning of the first record seen accordingly to
the query plan of execution chosen at that moment. It is NOT the earliest
record, it is not the first as actually stored in the table (in case the
query plan does something else than a table scan). All the fields that are
aggregated with First will come from the same record, by opposition to Min
which will retrieve the minimum from each different records (in a group).



f1 f2 f3
a 1 99
a 99 1



SELECT f1, FIRST(f2), FIRST(f3)
FROM somewhere
GROUP BY f1



would return the first or the second record, while


SELECT f1, MIN(f2), MIN(f3)
FROM somewhere
GROUP BY f1


will return


a 1 1




Hoping it may help,
Vanderghast, Access MVP
 
Thanks,

That explains it. For some reason I was thinking they
both functioned the same way which is not the case.

Xcelsoft
 
Back
Top