Hi Sergey,

Yeah, probably incomplete info there. I was thinking of the case where a table will have (at least) a clustered index which provides the "natural order", I.e. with a clustered index a non-ORDER BY query would return rows in the clustered index order. My bad.

Ok, I'll look at the code. I found a couple of ways to do it also. One I came up with and another I found on the SQL Server Magazine site. There really are no "efficient", simple ( I.e. without auxiliary tables, etc. ) ways to do it that I could find, since SQL Server has no concept of an Nth row like we think of in VFP.

The reason is I've been researching "paging" through data in SQL Server. I.e. based on a particular order, retrieving a "page" (or group) of rows, then next "page", previous "page", or an arbitrary "page" of rows.

I've found ways to do it on tables that aren't too large. For large tables the methods become expensive unless an auxiliary work table is created.

Bill

>
>There's no such thing as "the natural order" in the SQL Server. The data in the tables isn't stored in any particular order unless there's clustered index. What are you trying to do?
>Anyway, Here's couple of tways to do that. If table has primary key, than it would be faster to get first PK and than join result back to the source table as shown in #2.
>

USE NorthWind
>-- #1
>SELECT TOP 1 * FROM (
>	SELECT TOP 125 *
>		FROM [Order Details]
>		ORDER BY UnitPrice DESC
>	) 	dt1	
>-- # 2
>SELECT *
>	FROM (
>		SELECT TOP 1 *
>			FROM (
>				SELECT TOP 125 OrderID, ProductID
>					FROM [Order Details]
>					ORDER BY UnitPrice DESC
>				) 	dt1		
>		) dt2
>	JOIN [Order Details] odt	
>	ON odt.OrderID = dt2.OrderID
>		AND odt.ProductID = dt2.ProductID
>	
>