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!

I feel your pain, but I think you are stuck with wierd quirky syntax. — HLGEM, Sep 15 '10 at 13:33
And here I was just complaining about being stuck in MS SQL 2000. Guess I'll shut up now :) — LittleBobbyTables - Au Revoir, Sep 15 '10 at 13:44

score 2 · Answer 1 · answered Sep 15 '10 at 17:00

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

answered Sep 15 '10 at 17:00

I need only top 1, so I'm fine with it. But what is more expensive, nested select or temp table? – 100r Sep 16 '10 at 01:14
@100r, I think the correct answer to that is "it depends". The only way to be certain would be to try running both and see how they compare. – Sep 16 '10 at 09:49

score 0 · Answer 2 · answered Sep 15 '10 at 13:31

0

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

answered Sep 15 '10 at 13:31

Spooks

6,937
11
49
66

tried that but you cannot use order by when setting rowcount :/ – 100r Sep 16 '10 at 01:11
1

I wonder if you could do a rowcount, and inside have a select with your order – Spooks Sep 16 '10 at 15:15

Select TOP 1 in SQL Server 6.5

2 Answers2