Fetch records from sql server database table in parts

Suhani Mody

15y2.4k

Hi All,

I want to fetch records from a table in my sql server database in parts. Like, in one query I want to see first 1000 records, in next query next 1000 records. Likewise..

Is it possible with sql server ? I am using sql server 2008. While googling, I found LIMIT clause for mysql, but it does not work for sql server. Please help.

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dorababu Meka15y ago

Accepted answer

Try this

First 1000 records:

SELECT  TOP 1000 * 
FROM    mytable 
ORDER BY 
        mycolumn

General solution (supports offset)

SELECT  * 
FROM    ( 
        SELECT  *, 
                ROW_NUMBER() OVER (ORDER BY mycolumn) rn 
        FROM    mytable 
        ) q 
WHERE   rn BETWEEN 1001 AND 2000 
ORDER BY 
        mycolumn

Sam Hobbs15y ago
If you are going to process all records eventually, then you can read all in at once and then just use the first 1000 or whatever and then the next and so forth. It would be easier that way. Since you are doing it the other way, I won't say any more.
0
Vijay Yadav15y ago
If your query got resolved, please mark the answer
0
Dorababu Meka15y ago
Welcome Suhani :)

Mark as answer
0
Suhani Mody15y ago
It worked perfectly. Thanks a lot.. :)
0
Suhani Mody15y ago
yes vijay, will try that .
0
Suhani Mody15y ago
hi Dorababu,

what about next 1000 records ? ;)
0
Dorababu Meka15y ago
Have you tried using Top

select top 1000 * from tblname where condition

select top 2000 * from tblname where condition minus select top 1000 * from tblname where condition
0
Vijay Yadav15y ago
Hi Suhani,
You are right LIMIT clause doesn't work for Sql as it work with MySql as far as i am concerned.
I think it can be done by using loop, while fecthing the records from the database you can apply your logic if the record reach to the 1000th record you can break the loop and then you can fetch the next 1000 record.
0