Selecting a specific numbered range of rows.

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

General SQL Server Forums

New to SQL Server Programming

Selecting a specific numbered range of rows.

Author

Topic

tmcivery
Starting Member

46 Posts

Posted - 2006-07-26 : 11:24:29

Hello all, thanks in advance for any help you might be able to give.

I'm familiar with the Top command but I need something else to help in a project I'm working on.

I would like to select rows 1 through 100000 from a specific table in one query then 100001 through 200000 in a second query and 200001 through 300000 in a third and so on until I have gone through all rows. There happens to be 424000 in the table I'm working on.

Any help is appreciated.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 11:35:47

In v2005
declare @numrows int, @rowend int
select @numrows = 5, @rowend = 10 + 5
select top (@numrows) * from (select top (@rowend) * from sysobjects order by id) a order by id desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tmcivery
Starting Member

46 Posts

Posted - 2006-07-26 : 11:44:28

I'm sorry, I should have pointed out that I'm working on SQL 2000. I'm getting an incorrect syntax near '(' on line 3 with the query provided above.

The table name I'm working on is mailbox by the way. I'm a little confused on where to add that in the query just given.

Thanks for the quick response nr.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 11:46:45

declare @numrows int, @rowend int
select @numrows = 5, @rowend = 10 + 5

declare @sql varchar(8000)
select @sql = 'select top ' + convert(varchar(20),@numrows) + ' * from (select top ' + convert(varchar(20),@rowend) + ' * from mailbox order by id) a order by id desc
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tmcivery
Starting Member

46 Posts

Posted - 2006-07-26 : 11:51:39

Error when running the second query:

Server: Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark before the character string ' * from mailbox order by id) a order by id desc
exec (@sql)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 11:54:03

declare @numrows int, @rowend int
select @numrows = 5, @rowend = 10 + 5

declare @sql varchar(8000)
select @sql = 'select top ' + convert(varchar(20),@numrows) + ' * from (select top ' + convert(varchar(20),@rowend) + ' * from mailbox order by id) a order by id desc'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tmcivery
Starting Member

46 Posts

Posted - 2006-07-26 : 11:58:43

This query checks fine but when executed I get:

Invalid column name 'id'.

Any idea why that might be?

tmcivery
Starting Member

46 Posts

Posted - 2006-07-26 : 12:02:36

I just removed the order by id portions of the query out and it seemed to resolve the problem. The mailbox table doesn't have an ID column. I appreciate your help nr.

tmcivery
Starting Member

46 Posts

Posted - 2006-07-26 : 12:15:59

This query unfortunately starts me at the first record in the database every time I run it regardless of what numbers I add to the numrows or rowend values.

I need a query that will allow me to start at row 100000 for example and will display the next 100000 records so that I'm viewing rows 100000 to 200000.

What I need in the end is to split my 400000+ row table into 4 parts.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 12:28:37

What do you mean by row 100000?
That is what the order by id was for.
If you don't order by some column then "next" is a meaningless term.
Just choose a unique column (or columns) and put back the order by statements.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tmcivery
Starting Member

46 Posts

Posted - 2006-07-26 : 12:47:32

Ok, that looks like it's working like you intended it now. Just so I'm clear on what the query is doing I'd like to ask a couple questions.

Firstly the numrows= just allows me to choose the number of rows to be displayed?

Secondly the rowend= is letting me set the row to start at plus the number of rows after that start row?

Is this correct? Thanks!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-26 : 13:02:52

I've just come to this thread. It reminds me of this (which may be of use)...

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

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

tmcivery
Starting Member

46 Posts

Posted - 2006-07-26 : 13:54:32

Thanks Ryan. I believe I have nr's version working properly now. Right now I wish I was working on MYSQL. How easy is SELECT * FROM mailbox LIMIT 5,10 ! I wish Microsoft would institute something like that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources