How to select statment to data dividing to 3 parts of rows ?

Question

I have table have 6000 rows to books data table without Id I need to display data dividing to 3 parts

from 1 to 2000

from 2001 to 4000

from 4001 to 6000

so how to do that please ?

create table #books
(
ISBN nvarchar(100),
Author nvarchar(100)
)
insert into #books(ISBN,Author)
VALUES
('002-80023334','Michel'),
('002-70023334','Hazem'),
('002-30023334','raheem'),
('002-46023334','salah'),
('002-56023334','mamdooh'),
('002-70123334','henry'),
('002-80223334','maxoweel')

so How to make select statement to select data from table Books

from 1 to 2000

from 2001 to 4000

from 4001 to 6000

Answer 1

Hi Ahmed,

Here is a conceptual example for SQL Server 2012 onwards.
It is using SELECT with OFFSET / FETCH clauses.
Exactly what you need.

-- DDL and data population, start
DECLARE @tbl TABLE (
   ID INT PRIMARY KEY
   , [Description] VARCHAR(100) NOT NULL
   );

INSERT INTO @tbl
VALUES (1, 'One')
   , (2, 'Two')
   , (3, 'Three')
   , (4, 'Four')
   , (5, 'Five')
   , (6, 'Six')
   , (7, 'Seven')
   , (8, 'Eight')
   , (9, 'Nine')
   , (10, 'Ten')
   , (11, 'Elleven')
   , (12, 'Twelve')
   , (13, 'Thirteen');
-- DDL and data population, end

DECLARE @BatchNo INT = 1
   , @NumberOfBatchesTotal DECIMAL = 3
   , @RowsPerBatch INT
   , @RowTotal INT;

-- To calculate # of rows per each Batch
SET @RowTotal = (SELECT COUNT(*) FROM @tbl);
SET @RowsPerBatch = CEILING(@RowTotal/@NumberOfBatchesTotal);

WHILE @BatchNo <= @NumberOfBatchesTotal
BEGIN
 SELECT * FROM @tbl
 ORDER BY ID
 OFFSET (@BatchNo - 1) * @RowsPerBatch ROWS
 FETCH NEXT @RowsPerBatch ROWS ONLY

   SET @BatchNo += 1;
END;

Answer 2

Hi ahmedsalah，
I am not sure what field your table is sorted by, so I chose the author to sort.I did a test according to the data you provided:

 select * from (select * ,row_number() over(order by Author) rn from #books) t where rn between 1 and 2  
 select * from (select * ,row_number() over(order by Author) rn from #books) t where rn between 3 and 5  
 select * from (select * ,row_number() over(order by Author) rn from #books) t where rn between 6 and 7  

Your 6000 rows of data can be displayed separately according to the following code：

select * from (select * ,row_number() over(order by Author) rn from #books) t where rn between 1 and 2000  
select * from (select * ,row_number() over(order by Author) rn from #books) t where rn between 2001 and 4000  
select * from (select * ,row_number() over(order by Author) rn from #books) t where rn between 4001 and 6000  

If you have any question, please feel free to let me know.

If the response is helpful, please click "Accept Answer" and upvote it.

Regards,
Echo

Answer 3

You can check out NTILE function to see whether that works for your case.

;with mycte as (
select *, NTILE(3) OVER ( ORDER BY ISBN) grp
from #books
)
Select * from mycte
Where
grp=1
--or grp=2
--or grp=3

Answer 4

You can load data to the table variable with an IDENTITY column and then you can select the rows based on the IDENTITY column values:

DECLARE @Books TABLE (
 RowId int IDENTITY(1, 1),
 ISBN nvarchar(100),
 Author nvarchar(100)
);

INSERT INTO @Books (ISBN, Author)
SELECT ISBN, Author 
FROM #books 
--ORDER BY ISBN; -- Sorted by ISBN 
--ORDER BY Author; -- Sorted by Author 
-- Random

-- List first 2000 rows
SELECT ISBN, Author FROM @Books WHERE RowId <= 2000;
-- List 2000 rows from 2001 to 4000
SELECT ISBN, Author FROM @Books WHERE RowId > 2000 AND RowId <= 4000;
-- List 2000 rows from 4001 to 6000
SELECT ISBN, Author FROM @Books WHERE RowId > 4000 AND RowId <= 6000;