Mimic The MySQL LIMIT Feature in Microsoft SQL Server

Contributor Icon Contributed by johnnythawte Date Icon November 20, 2006  
Tag Icon Tagged: 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.

Previous recipe | Next recipe |
 
  • Kirill
    Doesn't work for some reason... Logic is there, but pulls out always the first 10, even if you say you want between 20-30. Any ideas, why this concept isn't working?
  • Name
    WITH temp AS
    (SELECT *, ROW_NUMBER() OVER (ORDER BY OrderColumn) AS rownum
    from MyTable)
    SELECT * FROM temp WHERE rownum BETWEEN 0 AND 10

    Is better.
  • Chris
    Sure looks more like MySQL's limit, but when you have a huge table with millions of records, it takes ages to return the desired rows.
blog comments powered by Disqus