Discussion Locked

Retreival Of Rows

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I want to retrieve only specific numbers of rows from SQL Server 7.0.
Say,I wanna retrieve first 100 records.
But without using
"Set Rowcount [ ]"

How to write a query for retrieving first 100 rows of any query.

Also I would like to know about the query which can fetch only specific Range of query ie; row no. 10 to Row no. 20.

This is possible in oracle using Rownum.

How to do in SQL server 7.0.

Please help me .................

5 replies

by mark.thomson

USE TOP

E.G SELECT TOP 100 [field list] FROM [TableName]

If your table has a Rownum column
use WHERE
E.G SELECT [field list] FROM [table name] WHERE Rownum between 10 and 20

If you want whatever the 10th - 20th rows happen to be regardless of their contents then you can create 2 almost identical views, 1 with TOP 10 and the other with TOP 20 and use an outer join (on the primary key of the table) with a filter for nulls

e.g SELECT * FROM vw_TOP20 LEFT OUTER JOIN vw_TOP10 ON vw_TOP20.PriKEY = vw_TOP10.Prikey WHERE vw_TOP10.Prikey IS NULL

This will work but has a lot of drawbacks

This discussion is closed to new replies.