How to return a range of rows?

"Briniken" <br******@yahoo.com> wrote in message
news:68**************************@posting.google.c om...

How can a SQL statement be written to return a specified range of
rows? For example:

-- tblContact
-- (
-- SSN char(9),
-- FirstName varchar(50),
-- LastName varchar(50)
-- )
-- This table contains 500 rows.

Select * from tblContact -- Return only rows 5 through 10

Thanks

Data in tables doesn't have any order, so you have to decide how to say
which are the 'first' 10 rows. Assuming that you want rows 5 to 10 when
ordered by LastName, then this is one possible solution:

select top 5 * from
(
select top 10 *
from tblContact
order by LastName asc) dt
order by LastName desc

Alternatively, you can look at the first example in this KB article:

http://support.microsoft.com/default...b;en-us;186133

If you add "having count(*) between 5 and 10" to the query, you should also
get the results you want.

Simon

Hi

There is not equivalent of a row number in SQL Server, therefore you need to
be able to order the values, but something like

SELECT TOP 5 SSN. FirstName, LastName FROM
( SELECT TOP 10 SSN. FirstName, LastName FROM tblContact ORDER BY SSN ASC )
A
ORDER BY SSN DESC

Will give you the rows, but not in order!

Also check out the solution in the following thread
http://groups.google.com/groups?hl=e...ewsposts%2BTOP

John
"Briniken" <br******@yahoo.com> wrote in message
news:68**************************@posting.google.c om...

How can a SQL statement be written to return a specified range of
rows? For example:

-- tblContact
-- (
-- SSN char(9),
-- FirstName varchar(50),
-- LastName varchar(50)
-- )
-- This table contains 500 rows.

Select * from tblContact -- Return only rows 5 through 10

Thanks