Need query help 1

[icemel]

Hi,

I need help writing this query:

I have a table with about 3 millions rows... and I need to select rows in bataches of five, meaning 600,000 rows at a time.

Each row is identified by a unique ID... however, the ID's are NOT in order. For example, ID sequence could go: 1, 2, 3, 10, 100, etc.

So I can't just say give me rows with ID's 1 - 600,000, then 601,000 to 1,200,000, etc.

How can I go about this?

Thanks much!

 

[vongrunt]

SQL2000 or 2005?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[03021979]

does each value of ID exist? Each from 1 to 3000000...

 

[icemel]

Yes, each ID exists.

SQL Server 2000

Is there a unique row identifier that SQL Server assigns to each row? Oracle has something like this I recall.

Thx

 

[vongrunt]

> Yes, each ID exists.

I don't understand... you said "the ID's are NOT in order."

Does it mean there are gaps in ID sequence or not?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

I think that the result set is not ordered by Id but by last name for example

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

There is a unique ID for each row.

The ID's are NOT in order.

The ID's are not sequential, either.

Meaning, 3 millionith row could have an ID of 50,000,000.

 

[icemel]

Yes, I remember now... Oracle has a way of identifying each row by a unique "rowid".

You can actually query an Oracle table by "rowid", although this is not an actual field in the table itself.

Does SQL Server have anything like this?

 

[vongrunt]

In other words: gimme first 600k rows with smallest ID values, then next 600k etc... True?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[icemel]

yes

 

[icemel]

If we were using 2005, I think something like this would work (however we are not).

USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;

I will work around this problem in the meantime.

Thanks anyways

 

[vongrunt]

There is one SQL2000 way - without temp tables. I'm just testing it... BRB soon.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Here it is... test on northwind DB, 100 rows per batch:

use Northwind
declare @firstid int, @lastid int
select @firstid = min(OrderID), @lastid = max(OrderID) from Orders

while @firstid < @lastid
begin
	-- select N rows, do with 'em whatever you want
	select top 100 * from Orders where OrderID >= @firstid order by OrderID

	-- advance pointer to next N-rows page
	select top 100 @firstid = 1+OrderID from Orders where OrderID >= @firstid order by OrderID
end

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[icemel]

yes, thanks - i see that i can use this code now for a new problem...