CODEWITHSUNDEEP
sqlt-sql
Cute Bear
Cute Bear

Reputation: 3291

select 100 records in range

Lets say I have a student table and there are 1000 records in it.

I would like to write such a query that should select only 100 records but I should be able to define the range. 

Ex. 100 records showing from 501 to 600
or 100 records showing from 101 to 200

P.S. Yes! There is a identity column but it consists of random numbers.

Thank you for your help.

Upvotes: 0

Views: 4926

Answers (3)

jTC
jTC

Reputation: 1350

This should do it.

SELECT a.* FROM (
     SELECT Row_Number() OVER(ORDER BY table.MySortCol) as RowNum,
     table.* -- columns you need
     FROM table
) a
WHERE a.RowNum between 1 and 100

This will give you a consistent result set even if you don't have a sequential id on the table, so you can perform meaningful paging or what have you.

Upvotes: 3

John Woo
John Woo

Reputation: 263933

use TOP[docs] for SQL Server

SELECT TOP 100 *
FROM tableName
WHERE columnName BETWEEN 0 AND 100

but in LIMIT for MySQL

SELECT *
FROM tableName
WHERE col BETWEEN 0 AND 100
LIMIT 100

UPDATE 1

WITH sampleRec
AS
(
    SELECT column1, col2, col3,
           ROW_NUMBER() OVER (ORDER BY colName) rn
    FROM yourTableName
)
SELECT column1, col2, col3
FROM sampleRec
WHERE rn BETWEEN 1 AND 100

Upvotes: 1

Darrrrrren
Darrrrrren

Reputation: 6088

If you are using MySQL use the LIMIT command.

SELECT * FROM MyTable LIMIT X, Y

Where X is your start record and Y is the number of records. So for your 501 to 600 you'd do LIMIT 501, 100.

This probably works in other SQL versions - just look up the LIMIT command for your system.

Upvotes: 1

Related Questions