How to return a range of rows?

[Briniken]

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

[Simon Hayes]

Re: How to return a range of rows?


"Briniken" <briniken@yahoo .com> wrote in message
news:68dae6d3.0 311012131.6f9fa f26@posting.goo gle.com...[color=blue]
> 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[/color]

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.aspx?scid=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

[John Bell]

Re: How to return a range of rows?

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=en&lr=&ie=UTF-8&threadm=3f953e2f%240%24255%24ed9e5944%40reading.news.pipex.net&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DISO-8859-1%26scoring%3Dd%26q%3DjBellNewsposts%2BTOP

John


"Briniken" <briniken@yahoo .com> wrote in message
news:68dae6d3.0 311012131.6f9fa f26@posting.goo gle.com...[color=blue]
> 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[/color]