CODEWITHSUNDEEP
sqlsql-serversql-limit
Stefan Frederiksen
Stefan Frederiksen

Reputation: 135

LIMIT on SQL query

How can I LIMIT the results to 10 in the following query? I use SQLSRV.

SELECT Id, Firstname, Surname FROM Person WHERE Firstname LIKE ?

Upvotes: 2

Views: 1376

Answers (3)

codingbadger
codingbadger

Reputation: 44032

The answer by kevingessner is certainly the easiest way.

I just thought I would throw some alternatives in for fun.

SET ROWCOUNT 10

 SELECT Id, Firstname, Surname FROM Person WHERE Firstname LIKE ?

SET ROWCOUNT 0

Or a more convoluted way:

With q 
as 
(
Select ROW_NUMBER() Over(Order by Id) as rn,
        Id,
        Firstname,
        Surname 
FROM Person WHERE Firstname LIKE ?
)
Select * 
From q
where q.rn <= 10

Upvotes: 0

sam
sam

Reputation: 340

use

select top(10) Id, Firstname, Surname ....

Upvotes: 1

kevingessner
kevingessner

Reputation: 18995

Use TOP:

SELECT TOP 10 Id, Firstname, Surname FROM Person WHERE Firstname LIKE ?

Upvotes: 6

Related Questions