How to return a range of rows?

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"Briniken" <briniken@.yahoo.com> wrote in message
news:68dae6d3.0311012131.6f9faf26@.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/defaul...kb;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=...Newsposts%2BTOP

John

"Briniken" <briniken@.yahoo.com> wrote in message
news:68dae6d3.0311012131.6f9faf26@.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