Selecting a subset of an ordered SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting a subset of an ordered SQL query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-12 : 08:36:16

steve writes "For example, let's say that

SELECT * FROM Table WHERE CreateDate > 1/1/2004 ORDER BY CreateDate

returns lots and lots of rows, each of which carries a lot of data. I want to write a query that will select them, say, 10 at a time. I can get the top 10 cheaply in MSSQL with "SELECT TOP 10 * FROM...", but is there a way to get #11-20, #21-30, and so forth other than, say, a combination of "SELECT id FROM Table...", plugging those into an array, and re-SELECTing certain rows?

Steve"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-12 : 09:00:16

It is something like the following

select * from(
select top 10 * from
(SELECT top 20 * FROM Table WHERE CreateDate > 1/1/2004 ORDER BY CreateDate Desc) t
ORDER BY CreateDate ) t
ORDER BY CreateDate

select * from(
select top 10 * from
(SELECT top 30 * FROM Table WHERE CreateDate > 1/1/2004 ORDER BY CreateDate Desc) t
ORDER BY CreateDate ) t
ORDER BY CreateDate

select * from(
select top 10 * from
(SELECT top 40 * FROM Table WHERE CreateDate > 1/1/2004 ORDER BY CreateDate Desc) t
ORDER BY CreateDate ) t
ORDER BY CreateDate
.
.
.
.

Madhivanan

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-12 : 09:14:36

Jeff has a nice technique for performing this - check out his blog:

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

VIG
Yak Posting Veteran

86 Posts

Posted - 2005-01-12 : 12:26:23

[code] declare @start int
declare @end int
set @start=...
set @end=...
select * from table t
where (select count(*) from table t1
where t1.CreateDate<=t.CreateDate and t1.CreateDate >'20040101'
) between @start and @end[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources