Is there any way to select, for example, first 10 rows of a table in T-SQL (working MSSQL)?
I think I saw something in Oracle defined as rownum meta variable, used in a following way
select * from Users where rownum<=10But what about MSSQL?
ROW_NUMBER (Window Function) ROW_NUMBER (Window Function) is a standard way of selecting the nth row of a table. It is supported by all the major databases like MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.
To skip a specified number of rows, use OFFSET , e.g. Will skip the first 20 rows, and then fetch 10 rows. Supported by newer versions of Oracle, PostgreSQL, MS SQL Server, Mimer SQL and DB2 etc. Show activity on this post.
From SQL Server 2005 it is possible to use an expression/variable in the TOP statement. In SQL Server 2000 it must be an integer value. Are you using a variable to specify the value 5000? If you are, you could use SET ROWCOUNT instead.
select top(@count) * from users
If @count
is a constant, you can drop the parentheses:
select top 42 * from users
(the latter works on SQL Server 2000 too, while the former requires at least 2005)
You can use Microsoft's row_number() function to decide which rows to return. That means that you aren't limited to just the top X results, you can take pages.
SELECT * FROM (SELECT row_number() over (order by UserID) AS line_no, * FROM dbo.User) as users WHERE users.line_no < 10 OR users.line_no BETWEEN 34 and 67
You have to nest the original query though, because otherwise you'll get an error message telling you that you can't do what you want to in the way you probably should be able to in an ideal world.
Msg 4108, Level 15, State 1, Line 3 Windowed functions can only appear in the SELECT or ORDER BY clauses.
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