I'm having trouble putting together a SQL statement. I know this is a smart group of guys, and I was hoping some of you have some SQL experience and might be able to help me out.

Using PHP and ODBC/Access, I'm trying to select a subset of records (the first x records) instead of pulling back the whole record set. In MySQL, I can use the LIMIT keyword, but in ANSI SQL (and apparently Access) that keyword doesn't exsist.

Everybody suggests the same thing..using TOP x, but that doesn't work for me.
SELECT TOP 10 keyfield, nonkeyfield FROM tablename ORDER BY keyfield returns the first 10 records as expected, but...
SELECT TOP 10 keyfield, nonkeyfield FROM tablename ORDER BY nonkeyfield returns more than 10 records when the values in nonkeyfield are not unique.

So, I need to do the equiv of:
SELECT keyfield, nonkeyfield FROM tablename ORDER BY nonkeyfield LIMIT 0,10
which works in MySQL, but not with any other SQL DBs.

Anybody have any suggestions?

- Chris
32GB MK2 090000664 Smoke
Queue # 2 (who the heck was 1?)