MS Access 97 question

[dbadmin]

I have a table Table1 with data like below

id text
--------------------
1 btest1
2 btest1
3 atest2
4 btest1
5 atest2
6 atest2

I need to write a query which will give me the min of id group by the text column. It goes like this

SELECT min(id), text
FROM Table1
GROUP BY text;

The result
-----------
Expr1000 text

3 atest2
1 btest1

This works fine, but when I try to order it by the first column in the resultset, I am getting error. My query looks like this

SELECT min(id), text
FROM Table1
GROUP BY text
ORDER BY min(id);

I tried the same query in Access 2000 and it works fine. Is there any work around for this in Access 97. I need the reultset like this

Expr1000 text

1 btest1
3 atest2

Thanks,
dbadmin

 

[sdk]

Hi,

Not sure why the sql isn't happy, but try replacing
ORDER BY min(id);

with
ORDER BY 1;

(I find this is often a handy syntax)


Cheers

S