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
MikeMCSDAsked:
Who is Participating?
 
ralmadaConnect With a Mentor 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

Open in new window

0
 
ralmadaCommented:
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

Open in new window

0
 
chapmandewConnect With a Mentor 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ralmadaCommented:
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
 
cyberkiwiConnect With a Mentor 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
 
chapmandewCommented:
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
 
chapmandewCommented:
1 = first 60K pages
2 = 60001 - 120000

etc
0
 
chapmandewCommented:
Im a goof...you're right.
0
 
chapmandewCommented:
Article I wrote on the subject...years ago

http://articles.techrepublic.com.com/5100-10878_11-6141785.html#
0
 
MikeMCSDAuthor 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.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
0
 
chapmandewCommented:
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
0
 
chapmandewCommented:
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
0
 
chapmandewCommented:
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
0
 
MikeMCSDAuthor 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
 
chapmandewCommented:
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
0
 
MikeMCSDAuthor Commented:
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 . .
0
 
MikeMCSDAuthor Commented:
worked, thanks . . how do I get the next 2 60,000 batches?
0
 
ralmadaCommented:
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.
 
0
 
cyberkiwiCommented:
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

Open in new window

0
 
MikeMCSDAuthor Commented:
thanks all
0
All Courses

From novice to tech pro — start learning today.