Select TOP 1 in SQL Server 6.5

Question

I beg you don't ask me why am I using SQL Server 6.5

There is no SELECT TOP command in SQL Server 6.5, and guess what, I need it :)

I need to perform something like

Select top 1 * from persons
where name ='Mike'
order by id desc

I've tried something with SET ROWCOUNT 1, but in that case you cannot use order by.

I end up with

Select top 1 * from persons
where id = (select max(id) from persons where name ='Mike' )

There must be better way!

Any suggestions?

Thanx!

Answer 1

SET ROWCOUNT 1 before your select statement, haven't tested this as I do not have mssql 6.5 (lucky I guess)

Answer 2

Try selecting into a temporary table, ordered by ID, then SET ROWCOUNT 1 and select * from temporary table. (This should work for any top N with SET ROWCOUNT N, while your existing solution will only work for top 1.)