SELECT... LIMIT?

[vongrunt]

Here comes a question: how to SELECT N records starting from position X? For example: gimme records between 91 and 100. I'd like to "skip" first X records from pure SQL, not using ADO & stuff.

Postgres and mySQL can do it. Oracle and DB/2 can do it w/ simple tricks (ROWNUM/OLAP functions). SQL2k has only SELECT TOP. Obviously, it always has to materialize X+N records... Is there any performance benefit by doing that?

 

[donutman]

If there's a pk that you are ordering by then
Select top 90 @LastKey=PK from Table order by PK
Select * from Table where PK>@LastKey order by PK
Does that work or do we have to try to rig a rowcounter?
-Karl

 

[vongrunt]

Good idea. Many real-life queries use ORDER BY unique attributes. When don't... add some fields into ORDER BY to ensure uniqueness and voila. Anyway, I'd like to see solution w/ rowcounter.

Here is some SQL circus art I reinvented so far. Sproc for sample data:

create procedure dbo.gen_sample_data( @row_count int )
as
if not exists(select * from sysobjects where xtype='U' and name='sample_data')
	create table sample_data
	(	id int NOT NULL,
		rand_value int NULL,
		description varchar(30) NULL
		primary key( id )
	)
else
	truncate table sample_data

declare @i int; set @i = 1
while @i <= @row_count
begin
	insert into sample_data (id, rand_value, description)
		values( @i, convert(int, rand()*@row_count), 'Sample row #' + str(@i) )
	set @i=@i+1
end
GO

Suppose that original query w/o LIMIT clause is SELECT * from sample_data ORDER BY id. This is your example:

declare @PK int
select top 90 @PK = id from sample_data order by id
select top 10 * from sample_data where id > @PK order by id

Horror #1:

select * from
(	select top 10 * from
	( select top 100 * from sample_data order by id ) X
	order by id desc
) Y 
order by id asc

Horror #2:

select top 10 X.* 
from sample_data X 
where X.id not in ( select top 90 id from sample_data Y order by id )
order by X.id

#1 is somewhat interesting as a concept. Performance-wise, it svcks.
#2 can be also done with EXISTS clause (basically the same) and LEFT JOIN trick (causes merge join even for small N/X).

 

[donutman]

My rowcounter idea was basically to do what you've done with the create table, but use a new identity column and insert the table of interest (sorted as you like) into the new table. But I've read that inserts don't necessarily get added in the order you specify! I don't understand that at all.
Maybe Yukon
-Karl