How to Select groups of rows by 60,000 from table

oops, typo there

declare @endrow int
declare @startrow int
set @endrow = 120000
set @startrow = 60001

select * from (select top(@endrow) * from Customer order by LName) t1
except
select * from (select top(@startrow) * from Customer order by LName) t2

Select allOpen in new window

The above is my preferred methond, but there are more alternatives like the ones in the question I've participated.
https://www.experts-exchange.com/questions/24294001/Select-10-to-25-Percent.html 

yep, thats why I did it.  could partition it into "pages" so you don't have to go through and do the math each time for it.  

1 = first 60K pages
2 = 60001 - 120000

etc

Im a goof...you're right.

Article I wrote on the subject...years ago

http://articles.techrepublic.com.com/5100-10878_11-6141785.html#

thanks guys, ..  not sure how to apply it,
got an error when I tried this:

select * from (
SELECT rowno = (row_number() over(order by Customer.Email)%60000) + 1, Customer.Email, Customer.LName as 'Last Name', Customer.FName as 'First Name',
Customer.Address1 as 'Address', Customer.City,
Customer.Region, Customer.PostalCode, Customer.Country, Product.Name as 'Product Name', Orders.OrderID
FROM Customer INNER JOIN
Orders ON Customer.CustomerID = Orders.CustomerID INNER JOIN
OrderDetail ON Orders.OrderID = OrderDetail.OrderID INNER JOIN
Product ON OrderDetail.ProductID = Product.ProductId
where CustomerID between 1088 and 61088
order by Customer.LName
)
where rowno = 1

select * from (
SELECT rowno = (row_number() over(order by Customer.LName)/60000) + 1, Customer.Email, Customer.LName as 'Last Name', Customer.FName as 'First Name',
Customer.Address1 as 'Address', Customer.City,
Customer.Region, Customer.PostalCode, Customer.Country, Product.Name as 'Product Name', Orders.OrderID
FROM Customer INNER JOIN
Orders ON Customer.CustomerID = Orders.CustomerID INNER JOIN
OrderDetail ON Orders.OrderID = OrderDetail.OrderID INNER JOIN
Product ON OrderDetail.ProductID = Product.ProductId
where CustomerID between 1088 and 61088

) a
where rowno = 1
order by Customer.LName

select * from (
SELECT rowno = (row_number() over(order by Customer.LName)/60000) + 1, Customer.Email, Customer.LName as 'Last Name', Customer.FName as 'First Name',
Customer.Address1 as 'Address', Customer.City,
Customer.Region, Customer.PostalCode, Customer.Country, Product.Name as 'Product Name', Orders.OrderID
FROM Customer INNER JOIN
Orders ON Customer.CustomerID = Orders.CustomerID INNER JOIN
OrderDetail ON Orders.OrderID = OrderDetail.OrderID INNER JOIN
Product ON OrderDetail.ProductID = Product.ProductId
where CustomerID between 1088 and 61088

) a
where rowno = 1
order by a.LName

crap:

select * from (
SELECT rowno = (row_number() over(order by Customer.LName)/60000) + 1, Customer.Email, Customer.LName as 'LastName', Customer.FName as 'First Name',
Customer.Address1 as 'Address', Customer.City,
Customer.Region, Customer.PostalCode, Customer.Country, Product.Name as 'Product Name', Orders.OrderID
FROM Customer INNER JOIN
Orders ON Customer.CustomerID = Orders.CustomerID INNER JOIN
OrderDetail ON Orders.OrderID = OrderDetail.OrderID INNER JOIN
Product ON OrderDetail.ProductID = Product.ProductId
where CustomerID between 1088 and 61088

) a
where rowno = 1
order by a.LastName

chap, I got :
Msg 209, Level 16, State 1, Line 9
Ambiguous column name 'CustomerID'.
Msg 209, Level 16, State 1, Line 9
Ambiguous column name 'CustomerID'.
for the last one you posted

select * from (
SELECT rowno = (row_number() over(order by Customer.LName)/60000) + 1, Customer.Email, Customer.LName as 'LastName', Customer.FName as 'First Name',
Customer.Address1 as 'Address', Customer.City,
Customer.Region, Customer.PostalCode, Customer.Country, Product.Name as 'Product Name', Orders.OrderID
FROM Customer INNER JOIN
Orders ON Customer.CustomerID = Orders.CustomerID INNER JOIN
OrderDetail ON Orders.OrderID = OrderDetail.OrderID INNER JOIN
Product ON OrderDetail.ProductID = Product.ProductId
--where CustomerID between 1088 and 61088

) a
where rowno = 1
order by a.LastName

I added :
where Customer.CustomerID between 1088 and 61088

Actually the above line should not have been there when I first posted
and tried the query, . . I was just trying something else at the time . .

worked, thanks . . how do I get the next 2 60,000 batches?

They already told you
where rowno = 2 will get second batch
If you are still interested you can also take a look at my approach and compare the speed.
 

Hi all,

Just quick correction.. again :)
Added a -1 so that the first batch returns 60000, not 59999

select * from (
SELECT rowno = (row_number() over(order by Customer.LName)-1)/60000 + 1, Customer.Email, Customer.LName as 'LastName', Customer.FName as 'First Name',
Customer.Address1 as 'Address', Customer.City,
Customer.Region, Customer.PostalCode, Customer.Country, Product.Name as 'Product Name', Orders.OrderID
FROM Customer INNER JOIN
Orders ON Customer.CustomerID = Orders.CustomerID INNER JOIN
OrderDetail ON Orders.OrderID = OrderDetail.OrderID INNER JOIN
Product ON OrderDetail.ProductID = Product.ProductId
--where CustomerID between 1088 and 61088

) a
where rowno = 1
order by a.LastName

Select allOpen in new window

thanks all