CODEWITHSUNDEEP
sqldatabase
dragan.stepanovic
dragan.stepanovic

Reputation: 3005

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

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?

Upvotes: 28

Views: 157025

Answers (7)

Rahul
Rahul

Reputation: 1336

SELECT TOP 10 * FROM TABLE_NAME ORDER BY ORDERED_UNIQUE_COLUMN DESC

ORDERED_UNIQUE_COLUMN could be your incrementing primary key or a timestamp

Upvotes: 0

Rameshwar Pawale
Rameshwar Pawale

Reputation: 682

Try this.

declare @topval int

set @topval = 5 (customized value)

SELECT TOP(@topval) * from your_database

Upvotes: 1

Jonathan
Jonathan

Reputation: 26649

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.

Upvotes: 10

RPL
RPL

Reputation: 79

Try this:

SELECT * FROM USERS LIMIT 10;

Upvotes: -2

ChickenMilkBomb
ChickenMilkBomb

Reputation: 945

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

Upvotes: 5

LukeH
LukeH

Reputation: 269668

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.

Upvotes: 8

Mehrdad Afshari
Mehrdad Afshari

Reputation: 422320

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)

Upvotes: 65

Related Questions