在SQL Server 6.5中选择TOP 1

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

求求你不要问我为什么使用SQL Server 6.5

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

SQL Server 6.5中没有SELECT TOP命令，猜猜是什么，我需要它:)

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.

我已尝试使用SET ROWCOUNT 1，但在这种情况下，您无法使用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!

感谢名单！

2 个解决方案

#1

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.)

尝试选择按ID排序的临时表，然后选择SET ROWCOUNT 1并从临时表中选择*。 （这应该适用于任何具有SET ROWCOUNT N的前N个，而您现有的解决方案仅适用于前1个。）

#2

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

在您的select语句之前设置了ROWCOUNT 1，没有测试过这个，因为我没有mssql 6.5（幸运的是我猜）