Mimic The MySQL LIMIT Feature in Microsoft SQL Server
Posted 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.
June 10, 2009 at 12:10 am, Kirill said:
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?
August 13, 2009 at 4:38 pm, Name said:
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.
October 05, 2009 at 5:56 pm, Chris said:
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.
June 21, 2010 at 1:45 pm, Lucian Oancia said:
i think it is enough with between, and without the temp. U cand scroll with the Between clause through an ID column.
August 16, 2010 at 8:55 pm, Alex Peta said:
Hi, so the code that works is :
WITH ORDERED AS (
SELECT ROW_NUMBER() OVER (ORDER BY category_id) AS RowNumber, category_id, NAME FROM [categories]
)
SELECT category_id,name FROM ORDERED WHERE RowNumber >= 3 AND RowNumber