Select number of rows [modified]

[MathewPV]

I have a table with 5000 rows I want to get the first 1000 rows in a single select query and the next 1001 to 5000 rows in another select query. A help from somebody will be deeply appreciated.

modified on Tuesday, August 3, 2010 11:34 PM

[J4amieC]

Which database server and version?

[MathewPV]

Sql server 2005

[Jorgen Andersson]

The first one is easy enough.

SELECT TOP 1000 * FROM MYTABLE

The other one could probably be done like this assuming you have a Primary Key MYTABLEID (or some other unique key):

SELECT *
FROM MYTABLE
WHERE MYTABLEID IN (SELECT TOP 5000 MYTABLEID FROM MYTABLE)
AND MYTABLEID NOT IN (SELECT TOP 1000 MYTABLEID FROM MYTABLE)

"When did ignorance become a point of view" - Dilbert

[MathewPV]

Thank you for the valuable information. Yes it worked

[J4amieC]

Please stop bolding every post. This is the right way to do paging with SQL 2005: http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx[^]

[J4amieC]

Although this is how it used to be done pre 2005, in SQL Server 2005 there was the ROW_NUMBER function which enabled much more efficient paging queries to be written.

[Jorgen Andersson]

Quite similar to Oracle then, which still lacks the TOP function.

"When did ignorance become a point of view" - Dilbert