How to implement LIMIT with Microsoft SQL Server?

Question

I have this query with mysql :

select * from table1 LIMIT 10,20

How can I do this with Microsoft sql ?

Answer 1

This is a multi step approach that will work in SQL2000.

-- Create a temp table to hold the data
CREATE TABLE #foo(rowID int identity(1, 1), myOtherColumns)

INSERT INTO #foo (myColumns) SELECT myData order By MyCriteria

Select * FROM #foo where rowID > 10

Answer 2

This is how I limit the results in MS SQL Server 2012

SELECT * 
FROM table1
ORDER BY columnName
  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

NOTE: OFFSET can only be used with or in tandem to ORDER BY.

To explain the code line OFFSET xx ROWS FETCH NEXT yy ROW ONLY

The "xx" is the record / row number you want to start pulling from in the table.
IE: If there is 40 record in table 1. The code above will start pulling from row 10.

The "yy" is the number of records / rows you want to pull from the table.
To build on the previous example.
IE: If table 1 has 40 records and you began pulling from row 10 and grab the NEXT set of 10 (yy).
That would mean, the code above will pull the records from table 1 starting at row 10 and ending at 20. Thus pulling rows 10 - 20.

Check out the link for more info on OFFSET

Answer 3

Starting with SQL SERVER 2012, you can use the OFFSET FETCH Clause:

USE AdventureWorks;
GO
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader 
ORDER BY SalesOrderID
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;
GO

http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx

This may not work correctly when the order by is not unique.

If the the query is modified to ORDER BY OrderDate, the result set returned is not as expected.

Answer 4

SELECT 
    * 
FROM 
    (
        SELECT 
            top 20              -- ($a) number of records to show
            * 
        FROM
            (
                SELECT 
                    top 29      -- ($b) last record position
                    * 
                FROM 
                    table       -- replace this for table name (i.e. "Customer")
                ORDER BY 
                    2 ASC
            ) AS tbl1 
        ORDER BY 
            2 DESC
    ) AS tbl2 
ORDER BY 
    2 ASC;

-- Examples:

-- Show 5 records from position 5:
-- $a = 5;
-- $b = (5 + 5) - 1
-- $b = 9;

-- Show 10 records from position 4:
-- $a = 10;
-- $b = (10 + 4) - 1
-- $b = 13;

-- To calculate $b:
-- $b = ($a + position) - 1

-- For the present exercise we need to:
-- Show 20 records from position 10:
-- $a = 20;
-- $b = (20 + 10) - 1
-- $b = 29;

Answer 5

SELECT  *
FROM    (
        SELECT  TOP 20
                t.*, ROW_NUMBER() OVER (ORDER BY field1) AS rn
        FROM    table1 t
        ORDER BY
                field1
        ) t
WHERE   rn > 10

Answer 6

This is one of the reasons I try to avoid using MS Server... but anyway. Sometimes you just don't have an option (yei! and I have to use an outdated version!!).

My suggestion is to create a virtual table:

From:

SELECT * FROM table

To:

CREATE VIEW v_table AS    
    SELECT ROW_NUMBER() OVER (ORDER BY table_key) AS row,* FROM table

Then just query:

SELECT * FROM v_table WHERE row BETWEEN 10 AND 20

If fields are added, or removed, "row" is updated automatically.

The main problem with this option is that ORDER BY is fixed. So if you want a different order, you would have to create another view.

UPDATE

There is another problem with this approach: if you try to filter your data, it won't work as expected. For example, if you do:

SELECT * FROM v_table WHERE field = 'test' AND row BETWEEN 10 AND 20

WHERE becomes limited to those data which are in the rows between 10 and 20 (instead of searching the whole dataset and limiting the output).