CODEWITHSUNDEEP
sqlsql-serverselectrange
Scott Shaffer
Scott Shaffer

Reputation: 39

How to select between the top 50 and 100 returned (queried) rows in sql?

Using MSSQL, how can I skip the first set of 50 records, return the next set of 50, and exclude any remaining records from a query. Essentially, something like what one would imagine the following code would do where identitynumber is the identity of the record.

SELECT TOP(100) * FROM dbo.users
WHERE users.name = 'John' AND IDENTITYNUMBER >=50 AND IDENTITYNUMBER <=100

Upvotes: 2

Views: 3591

Answers (3)

Svein Terje Gaup
Svein Terje Gaup

Reputation: 1578

Guess you need the OFFSET keyword.

SELECT employee_id, first_name, last_name, salary 
FROM employees 
ORDER BY salary DESC 
OFFSET 5 ROWS 
FETCH NEXT 5 ROWS ONLY;

Or in your case:

select * from dbo.Users
where name = 'John'
order by users.name
offset 50 rows
fetch next 50 rows only;

Source: https://www.sqltutorial.org/sql-fetch/

Upvotes: 5

Ifey Oyewumi
Ifey Oyewumi

Reputation: 1

Give something like this a go

SELECT * FROM dbo.users
WHERE users.name = 'John'
ORDER BY users.name
OFFSET 50 ROWS
FETCH NEXT 50 ROWS ONLY;

Upvotes: -1

Rafael Souza
Rafael Souza

Reputation: 59

You could use ROW_NUMBER()

CREATE TABLE #users ( id int, name varchar(100) )

INSERT INTO #users
VALUES (1,'John'),(2,'John'),(3,'John'),(4,'Not John'),(5,'John')

SELECT TOP(2) x.*
FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY name) AS rowNumber
        FROM #users
        WHERE name = 'John'
     ) AS x
WHERE x.rowNumber > 2

DROP TABLE #users

Check this fiddle

Upvotes: 1

Related Questions