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

Question

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 lt  10  But what about MSSQL

User · Answer

Try this   declare  topval int  set  topval   5  customized value   SELECT TOP  topval    from your database

User · Answer

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  lt  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

User · Answer

Try this   SELECT   FROM USERS LIMIT 10

User · Answer

SELECT TOP 10   FROM Users   Note that if you don t specify an ORDER BY clause then any 10 rows could be returned  because  first 10 rows  doesn t really mean anything until you tell the database what ordering to use

User · Answer

SELECT TOP 10   FROM TABLE NAME ORDER BY ORDERED UNIQUE COLUMN   DESC   ORDERED UNIQUE COLUMN could be your incrementing primary key or a timestamp

User · Answer

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

User · Answer

You can also use rowcount  but TOP is probably better and cleaner  hence the upvote for Mehrdad  SET ROWCOUNT 10 SELECT   FROM dbo Orders WHERE EmployeeID   5 ORDER BY OrderDate  SET ROWCOUNT 0

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

Examples related to sql

Examples related to database