select records After top 1000 rows

Question

I want to select records from 1000 to 2000 rows and so on in batch of 1000.

I have written query to select top 1000 records but how can i select from 1000 to 2000.

can you help me with a query that can select those records.

SELECT TOP 1000 *
FROM tblProductInformation p1 INNER JOIN tblProduct1 p 
ON p.productname = p1.productname

You should add an explicit ordering when using a top clause, without it, the order is not guarenteed, even though it might look it. — Andrew, Feb 29 '12 at 10:30
What you are looking for is called *paging*. Just search for "paging sql server" and you'll get plenty of results. — MicSim, Feb 29 '12 at 10:36
possible duplicate of [Best paging solution using SQL Server 2005?](http://stackoverflow.com/questions/3747186/best-paging-solution-using-sql-server-2005) — Tony, Feb 29 '12 at 10:38

Pongsathon.keng · Accepted Answer · 2012-02-29T10:44:53.393

3

I think you need to order on specific a column, for example order on the primary key.

SELECT *
FROM
(
   SELECT tbl.*, p.*, ROW_NUMBER() OVER (ORDER BY ProductID_PRIMARYKEY) rownum
  FROM  tblProductInformation as tbl INNER JOIN tblProduct1 p 
  ON p.productname = p1.productname 
) seq
WHERE seq.rownum BETWEEN 1000 AND 2000

edited Feb 29 '12 at 10:44

answered Feb 29 '12 at 10:35

Pongsathon.keng

1,417
11
14

i want to use join query how can i use it in your above query – user818671 Feb 29 '12 at 10:41
You can join. I updated my comment. The key of this solution is ROW_NUMBER() OVER (ORDER BY ProductID_PRIMARYKEY). this statement will help you to order result and get row 1000-2000 by using WHERE seq.rownum BETWEEN 1000 AND 2000 – Pongsathon.keng Feb 29 '12 at 10:46

Tim Schmelter · Answer 2 · 2012-02-29T10:52:52.290

WITH cte AS(
    SELECT ROW_NUMBER()OVER(Order By p1.productname ASC, p1.ID ASC) As RowNum
    ,p1 .*
    from tblProductInformation p1 
    inner join tblProduct1 p on p.productname = p1.productname
)
SELECT * FROM cte 
WHERE RowNum BETWEEN @FromRowNum AND @ToRowNum

ROW_NUMBER: http://msdn.microsoft.com/en-us/library/ms186734.aspx
Paging on SQL-Server: http://www.mssqltips.com/sqlservertip/1175/page-through-sql-server-results-with-the-rownumber-function/

score 0 · Answer 3 · answered Oct 31 '14 at 15:41

Answer Late .. but could be helpful for some one coming here ... simple one Another simple approach ...

You can create a similar table "tblProductInformation_tmp" OR #tblProductInformation_tmp - with an extra column "UniqueID" and make that auto-increment IDENTITY column. then just insert the same data to table :

insert * into tblProductInformation_tmp 
select * from tblProductInformation

Now its simple ryt : select * from tblProductInformation_tmp where UniqueID < 1001 select * from tblProductInformation_tmp where UniqueID between 1001 and 2001

:) Dont forget to delete : tblProductInformation_tmp

Rigin

score 0 · Answer 4 · answered Feb 29 '12 at 10:34

0

WITH Results AS (
    select TOP 1000 f.*, ROW_NUMBER() OVER (ORDER BY f.[type]) as RowNumber
        from tblProductInformation f
) select *
    from Results
    where RowNumber between 1001 and 2000

tutorial

answered Feb 29 '12 at 10:34

Vikram

8,235
33
47

select records After top 1000 rows

4 Answers4

Linked