Sergey,

>It's valid query. Are you sure it's '1' (one) not 'l' (lower L)?

First, you helped me win my own bet. I was betting that the first answer would come from you. Now I can take myself out for a steak dinner. :-)

Second, it's not an L. I have tried with TOP 5 and still get the same error (and it's not an S). Is there some kind of system setting that would affect this? I'm completely stumped.


>
>>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?