Agregate query (first) not returning "first" record from a sub que

[David Witteried]

I am sure in Access V2 - 95 that if you used made a query (with an order by
clause), then used that query as a sub-query to select the first record from
a grouping withing the subquery, that you would get the correct record. In
Access 2003 however, the agregate query seems to revert to the table order
and ignores the sequence of the sub-query. Is there a way to make this work
w/o creating a temporary table useing the sub-query, then using the resulting
table as the basis of the agregating query?

Example:

Data in table:
ID Group Code
1 A D
2 B C
3 A A
4 B B

Sorted by Sub-Query (Group Asc, Code Asc):
ID Group Code
3 A A
1 A D
4 B B
2 B C

Agregate query selects first ID and Code from each group:
ID Group Code
3 A A
4 B B

This is what Access actually returns:
ID Group Code
1 A D
2 B C

 

[Michael Gramelspacher]

I am sure in Access V2 - 95 that if you used made a query (with an order by
clause), then used that query as a sub-query to select the first record from
a grouping withing the subquery, that you would get the correct record. In
Access 2003 however, the agregate query seems to revert to the table order
and ignores the sequence of the sub-query. Is there a way to make this work
w/o creating a temporary table useing the sub-query, then using the resulting
table as the basis of the agregating query?

Example:

Data in table:
ID Group Code
1 A D
2 B C
3 A A
4 B B

Sorted by Sub-Query (Group Asc, Code Asc):
ID Group Code
3 A A
1 A D
4 B B
2 B C

Agregate query selects first ID and Code from each group:
ID Group Code
3 A A
4 B B

This is what Access actually returns:
ID Group Code
1 A D
2 B C

try:

SELECT T.ID, T.Group, T.Code
FROM (SELECT T2.ID,
T2.Group,
T2.Code,
(SELECT COUNT(*)
FROM Table2 AS T3
WHERE T3.Group = T2.Group
AND T3.ID >= T2.ID) AS RowNumber
FROM Table2 AS T2) AS T
WHERE T.RowNumber = 1;