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

I have a table with 170,597 rows in it.
I need to Select the first 60,000 then the second 60,000, etc..
But I have no Identity column I can use for a count.
How else can I do this?  thanks   SQL Server 2005

SELECT Customer.Email, Customer.LName ......
FROM  Customer
where ???
order by Customer.LName

LVL 16
Who is Participating?

x

Commented:
One possibility is using EXCEPT

``````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
``````
0

Commented:
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
``````
0

Commented:
no problem:

select * from (
SELECT rowno = (row_number() over(order by Customer.Email)%60000) + 1, Customer.Email, Customer.LName ......
FROM  Customer
)
where rowno = 1
0

Commented:
The above is my preferred methond, but there are more alternatives like the ones in the question I've participated.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24294001.html
0

Commented:
Use row_number like chapmandew's, but don't use %, use / in the same place. Rowno 1 gives first 60k, 2 is next etc
0

Commented:
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.
0

Commented:
1 = first 60K pages
2 = 60001 - 120000

etc
0

Commented:
Im a goof...you're right.
0

Commented:
Article I wrote on the subject...years ago

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

Author Commented:
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.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
0

Commented:
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.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
0

Commented:
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.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
0

Commented:
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.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
0

Author Commented:
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
0

Commented:
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.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
0

Author Commented:
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 . .
0

Author Commented:
worked, thanks . . how do I get the next 2 60,000 batches?
0

Commented:
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.

0

Commented:
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.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
``````
0

Author Commented:
thanks all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.