Mimic The MySQL LIMIT Feature in Microsoft SQL Server

Posted November 20, 2006 by johnnythawte in Database

MySQL includes a nifty feature that lets you select only rows 1-10, 11-20, or any set you want. Microsoft SQL Server does not include this feature. This recipe will show you how to mimic the same feature easily without stored procedures.


Microsoft’s T-SQL includes the TOP syntax, which allows you to select only the top x number of records from your SQL query. This is very useful for testing purposes or selecting a single record, but less useful for production applications where you might want to do some sort of paging.

What we will do is use the TOP feature in reverse. We will select the top 20 records in a particular order, and then reverse the order and select the top 10 of those.

SELECT TOP 10 *
FROM (SELECT TOP 20 * FROM Orders ORDER BY OrderDate) as T
ORDER BY OrderDate DESC

The subselect SQL statement will return the top 20 orders by date. Then we will select the top 10 of those and reverse the sort order back.

If we want to select orders 20-30 in the list, we would simply do this:

SELECT TOP 10 *
FROM (SELECT TOP 30 * FROM Orders ORDER BY OrderDate) as T
ORDER BY OrderDate DESC

It’s probably not useful for a true production application, but in a pinch this works pretty well.

The Conversation

Follow the reactions below and share your own thoughts.