Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to select first N rows from a table in T-SQL?

Tags:

sql

database

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<=10 
But what about MSSQL?
like image 815
dragan.stepanovic Avatar asked Oct 11 '09 22:10

dragan.stepanovic


People also ask

How do I select nth row in SQL?

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.

How do I select only 10 rows in SQL Server?

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.

How do I select top 2000 rows in SQL?

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.


2 Answers

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)

like image 200
mmx Avatar answered Oct 02 '22 15:10

mmx


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. 
like image 29
Jonathan Avatar answered Oct 02 '22 17:10

Jonathan