Over the TOP

You can also use the temporary table solution for this problem. You can store the orders sorted by CustomerID and OrderID in the temporary table with the autogenerated row numbers, as the following code shows:

TRUNCATE TABLE #Orders

INSERT INTO #Orders(OrderID, CustomerID,
  EmployeeID, OrderDate)
  SELECT OrderID, CustomerID, EmployeeID, OrderDate
  FROM Orders
  ORDER BY CustomerID, OrderID

Now, you can request the three orders in which the customer's row number is less than that customer's minimum row number plus 3:

SELECT O.CustomerID, O.OrderID
FROM
    #Orders AS O
  JOIN
    (SELECT CustomerID, MIN(RowNum) AS MinRowNum
    FROM #Orders
    GROUP BY CustomerID) AS M ON  O.CustomerID =
	  M.CustomerID
	  AND O.RowNum < M.MinRowNum + 3
ORDER BY O.CustomerID

Using a UDF to Get Rows m to n
You can use a user-defined function (UDF) to get rows m to n. In the same way that the temporary table solution does, the UDF populates a table variable with the table rows and the row number, which the IDENTITY column generates. Listing 1 shows the script that creates the ufn_GetOrders() function.

The function first stores all the rows from the Orders table in the @Orders table variable, then deletes all irrelevant rows. A more efficient way might be to store the TOP @lastrow rows in the table variable and then delete all rows where RowNum is less than @firstrow. The problem is that the n part of the TOP n query can't be a variable; it must be a constant. This problem usually occurs when you need a small number of rows in a large table, but the solution is nice when you're using small tables.

To invoke the UDF to get orders 6 to 10, issue the following query:

SELECT * FROM ufn_GetOrders(6, 10)
ORDER BY RowNum

Extending the Capabilities
This article shows you how to extend the capabilities of TOP queries and provide solutions beyond TOP to limit the number of rows in the result. Using these techniques will help you write better-performing code when you need to manipulate groups of rows.