Menu
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?
895
The SQL LIMIT clause constrains the number of rows returned by a SELECT statement. For Microsoft databases like SQL Server or MSAccess, you can use the SELECT TOP statement to limit your results, which is Microsoft's proprietary equivalent to the SELECT LIMIT statement.
The LIMIT clause is used to set an upper limit on the number of tuples returned by SQL. It is important to note that this clause is not supported by all SQL versions. The LIMIT clause can also be specified using the SQL 2008 OFFSET/FETCH FIRST clauses. The limit/offset expressions must be a non-negative integer.
The SQL SELECT LIMIT statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a limit value. TIP: SELECT LIMIT is not supported in all SQL databases. For databases such as SQL Server or MSAccess, use the SELECT TOP statement to limit your results.
The SQL LIMIT clause restricts how many rows are returned from a query. The syntax for the LIMIT clause is: SELECT * FROM table LIMIT X;. X represents how many records you want to retrieve. For example, you can use the LIMIT clause to retrieve the top five players on a leaderboard.
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
SELECT TOP 1 * FROM Employee ORDER BY newid()
You have to use newid() for it to be evaluated once per row.
6
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
