I am trying to execute a Top n query on SQL Server 2000 and cannot see what is wrong with this select statement:

select top 1 * from mytable order by myfield

I have also tried it with a fieldname instead of * (the same field used in the order by clause). Both approaches give me this error message:

Incorrect syntax near '1'

I have tried via CursorAdapter and also in Query Analyzer. I can get what I want by using the SET ROWCOUNT before and after, as in:

oCA.SelectCmd="set rowcount 1;select * from mytable order by myfield;set rowcount 0"

What am I missing?