How to use LIMIT keyword in SQL Server 2005?

Question

I have found a way to select random rows from a table in this post. A suggestion is to use the following query:

SELECT * FROM employee ORDER BY RAND() LIMIT 1

But when I run this query in MS SQL 2005, I get the following error message

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'LIMIT'.

Can anyone tell me where am I wrong? Doesn't MS SQL support LIMIT? If so, then how can I do this?

Answer 1

If you take a look at the SELECT statement in SQL Server Books Online, then you'll see that you can limit the resultset by using the TOP keyword.

SELECT TOP 1 * FROM employee

Answer 2

See also Emulate MySQL LIMIT clause in Microsoft SQL Server 2000 and Row Offset in MS SQL Server.

Answer 3

SELECT TOP 1 * FROM Employee ORDER BY newid()

You have to use newid() for it to be evaluated once per row.

Answer 4

I'm using this fairly simple one (SQL2005) to limit the number of rows returned, which will work with a value provided by a stored procedure parameter as well.

DECLARE @Limit int
SET @Limit = 10
SELECT TOP (@Limit) Col1, Col2 FROM SomeTable