paging with disconnected data

[Paul Wilson]

Hi guys,

I've learnt thata SQLDataAdapters support paging with the overloaded FILL
method (having start record & max records as parameters).
& also learnt that the ideal way to achieve paging is not using this,
rather, using the TOP & ORDER BY keyword in the SELECT command to limit the
records to what i need.

Okay,
Following is the scenario,
Table = AdrBook
Fields = Id (Key field, autoincrement - Integer)
ContactId (Candidate Key, string)
First Name (string)
Age (integer)

Now, there are 1000 records (1000 contacts)

In the form (windows application), i put a request to the server to fetch
the records 10-20 (via a webservice).
The data should be sorted by the Age field in Ascending order.

The select command i wouild use is

SELECT TOP 10 * FROM AdrBook WHERE ID IN
(SELECT TOP 20 ID FROM AdrBook ORDER BY Age ASC)
ORDER BY Age DESC

Using the above select, i get exactly what i want.
This will work for multiple ORDER BY clauses. The inner select will have the
actual ORDER BY, but the
outer select will have the reversed ORDER BY.

Now, my problem.

The final output gives me exaclty what i want.
But, the data is ordered in the reverse order. (how can i fix this).

So i fixed this, by using the following SELECT

SELECT TOP 10 * FROM AdrBook WHERE ID IN
(SELECT TOP 10 ID FROM WHERE ID IN
(SELECT TOP 20 ID FROM AdrBook ORDER BY Age ASC)
ORDER BY Age DESC)
ORDER BY Age

Now, as you can see, i've got 3 selects from AdrBook.
1). Does this have any implications on performance???

2). Does anyone have a better way ot doing paging?

Regards,
Paul