147

I have this query with MySQL:

select * from table1 LIMIT 10,20

How can I do this with SQL Server?

Dale K
  • 16,372
  • 12
  • 37
  • 62
Bigballs
  • 3,361
  • 9
  • 28
  • 27

18 Answers18

138

Starting SQL SERVER 2005, you can do this...

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 10 AND 20;

or something like this for 2000 and below versions...

SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC
Leon Tayson
  • 4,287
  • 5
  • 33
  • 35
  • 6
    The 2nd query fails if you have e.g. 14 rows in the table. It gives you rows 5 through 14, but you want rows 11 through 14. In general, it fails for the last "page" of a result, unless the total rows are a multiple of that "page" size. – Bill Karwin Mar 02 '09 at 20:13
  • 163
    Such a simple thing needs to be made so difficult by MS yet again! – Martin Apr 14 '11 at 10:56
  • Here's what worked for me in SQL Server Management Studio 2017: SELECT * FROM [dbo]. WHERE @@ROWCOUNT BETWEEN and – Artorias2718 Feb 07 '19 at 15:44
  • Just Fantastic, It's works like charm in MS SQL Server 2017 select Statement – PatsonLeaner Jun 13 '19 at 12:57
  • This is not a good answer for any of the current SQL Server versions. This will scan the entire table to calculate the `ROW_NUMBER()` before filtering. – Panagiotis Kanavos May 21 '21 at 08:23
66

Clunky, but it'll work.

SELECT TOP 10 * FROM table WHERE id NOT IN (SELECT TOP 10 id FROM table ORDER BY id) FROM table ORDER BY id

MSSQL's omission of a LIMIT clause is criminal, IMO. You shouldn't have to do this kind of kludgy workaround.

ceejayoz
  • 165,698
  • 38
  • 268
  • 341
  • Do you have another suggestion to bypass this? – Bigballs Mar 02 '09 at 20:00
  • I did a lot of Googling the last time I had to deal with MSSQL and this was the best solution I found. Not pleasant, but it works. – ceejayoz Mar 02 '09 at 20:05
  • This solution works only if the result set includes a column that is unique. It's not a general solution to mimic LIMIT for any query. – Bill Karwin Mar 02 '09 at 20:09
  • 1
    I'm in a similar quandary right now... However, in my case I'm hosed... It's even more criminal when so called 'expert' dba's decide that an unique key in unnecessary in a table... ANY table... Don't even bring up the subject of foreign keys and constraints! – Andrew Rollings Mar 02 '09 at 20:10
  • Problem with this one is, it doesn't handle WHERE clauses very well... I'm going to try temp tables, as its not working for me. – nasty pasty Nov 03 '09 at 05:09
48

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 query is modified to ORDER BY OrderDate, the result set returned is not as expected.

HoldOffHunger
  • 10,963
  • 6
  • 53
  • 100
user4047259
  • 481
  • 4
  • 2
19

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, i.e: If there are 40 records 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: 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

fhcimolin
  • 565
  • 1
  • 5
  • 22
Jeremy
  • 472
  • 1
  • 4
  • 14
  • Agree to this. For me I need to use this condition for my custom native query ,hence vanilla findBy clauses of JPA didn't helped much. This options worked as expected. Please see this page as best references I have seen: https://www.sqlservertutorial.net/sql-server-basics/sql-server-offset-fetch/ – peetTechs Aug 26 '20 at 17:13
18

This is almost a duplicate of a question I asked in October: Emulate MySQL LIMIT clause in Microsoft SQL Server 2000

If you're using Microsoft SQL Server 2000, there is no good solution. Most people have to resort to capturing the result of the query in a temporary table with a IDENTITY primary key. Then query against the primary key column using a BETWEEN condition.

If you're using Microsoft SQL Server 2005 or later, you have a ROW_NUMBER() function, so you can get the same result but avoid the temporary table.

SELECT t1.*
FROM (
    SELECT ROW_NUMBER OVER(ORDER BY id) AS row, t1.*
    FROM ( ...original SQL query... ) t1
) t2
WHERE t2.row BETWEEN @offset+1 AND @offset+@count;

You can also write this as a common table expression as shown in @Leon Tayson's answer.

Community
  • 1
  • 1
Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
  • ROW_NUMBER() OVER (ORDER BY) gets points for being valid in ANSI SQL:2003, although support in DBMSs other than SQL Server is very spotty. And it's pretty clunky of course... – bobince Mar 02 '09 at 22:39
  • @bobince: It turns out Oracle, Microsoft SQL Server 2005, IBM DB2, and PostgreSQL 8.4 all support window functions. That covers an huge majority of the SQL market. Support is only spotty if you use MySQL, SQLite or an old version of the DB's above. – Bill Karwin Mar 17 '10 at 15:34
12
SELECT  *
FROM    (
        SELECT  TOP 20
                t.*, ROW_NUMBER() OVER (ORDER BY field1) AS rn
        FROM    table1 t
        ORDER BY
                field1
        ) t
WHERE   rn > 10
Quassnoi
  • 381,935
  • 83
  • 584
  • 593
  • Well, I just checked, SQL Server turned out to be smart enough to stop on ROW_NUMBER() conditions, if there is an indexed column in ORDER BY clause. – Quassnoi Mar 02 '09 at 20:36
9

Syntactically MySQL LIMIT query is something like this:

SELECT * FROM table LIMIT OFFSET, ROW_COUNT

This can be translated into Microsoft SQL Server like

SELECT * FROM 
(
    SELECT TOP #{OFFSET+ROW_COUNT} *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rnum 
    FROM table
) a
WHERE rnum > OFFSET

Now your query select * from table1 LIMIT 10,20 will be like this:

SELECT * FROM 
(
    SELECT TOP 30 *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rnum 
    FROM table1
) a
WHERE rnum > 10 
3
SELECT TOP 10 * FROM table;

Is the same as

SELECT * FROM table LIMIT 0,10;

Here's an article about implementing Limit in MsSQL Its a nice read, specially the comments.

Ólafur Waage
  • 64,767
  • 17
  • 135
  • 193
  • 2
    Thanks, but I want the record between 10 and 20, there's a way to do it? – Bigballs Mar 02 '09 at 19:57
  • 6
    This answer doesn't respond to the origin question, but it IS useful if someone like me needs to know how to get the first N results and got here via google etc... – brianlmerritt Jun 22 '17 at 12:09
2

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).

lepe
  • 22,543
  • 9
  • 85
  • 99
1
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;
Julian Moreno
  • 1,002
  • 3
  • 14
  • 29
1

Must try. In below query, you can see group by, order by, Skip rows, and limit rows.

select emp_no , sum(salary_amount) from emp_salary
Group by emp_no 
ORDER BY emp_no 
OFFSET 5 ROWS       -- Skip first 5 
FETCH NEXT 10 ROWS ONLY; -- limit to retrieve next 10 row after skiping rows
M Danish
  • 346
  • 1
  • 5
1

Easy way

MYSQL:

SELECT 'filds' FROM 'table' WHERE 'where' LIMIT 'offset','per_page'

MSSQL:

SELECT 'filds' FROM 'table' WHERE 'where' ORDER BY 'any' OFFSET 'offset' 
ROWS FETCH NEXT 'per_page' ROWS ONLY

ORDER BY is mandatory

Turendu
  • 11
  • 2
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
Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
souLTower
  • 596
  • 1
  • 4
  • 5
0

In SQL there's no LIMIT keyword exists. If you only need a limited number of rows you should use a TOP keyword which is similar to a LIMIT.

Mitul Panchal
  • 448
  • 5
  • 5
0

If your ID is unique identifier type or your id in table is not sorted you must do like this below.

select * from
(select ROW_NUMBER() OVER (ORDER BY (select 0)) AS RowNumber,* from table1) a
where a.RowNumber between 2 and 5



The code will be

select * from limit 2,5
Rahul Agarwal
  • 3,743
  • 6
  • 24
  • 40
0

better use this in MSSQLExpress 2017.

SELECT * FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) as [Count], * FROM table1
) as a
WHERE [Count] BETWEEN 10 and 20;

--Giving a Column [Count] and assigning every row a unique counting without ordering something then re select again where you can provide your limits.. :)

0

One of the possible way to get result as below , hope this will help.

declare @start int
declare @end int
SET @start = '5000';  -- 0 , 5000 ,
SET @end = '10000'; -- 5001, 10001
SELECT * FROM ( 
  SELECT TABLE_NAME,TABLE_TYPE, ROW_NUMBER() OVER (ORDER BY TABLE_NAME) as row FROM information_schema.tables
 ) a WHERE a.row > @start and a.row <= @end
Pragnesh Karia
  • 479
  • 1
  • 6
  • 14
-2

If i remember correctly (it's been a while since i dabbed with SQL Server) you may be able to use something like this: (2005 and up)

SELECT
    *
   ,ROW_NUMBER() OVER(ORDER BY SomeFields) AS [RowNum]
FROM SomeTable
WHERE RowNum BETWEEN 10 AND 20
Kris
  • 36,072
  • 8
  • 69
  • 94
  • SQL Server 2012: Msg 207, Level 16, State 1, Line 5 Invalid column name 'RowNum'. – e-info128 Jun 24 '13 at 19:11
  • sounds like you have a typo in your statement somewhere. RowNum is the name we assign to the expression. Post your problem with the source and the community will help you – Kris Jun 25 '13 at 13:41
  • This is not valid syntax. You can't reference in `WHERE` an alias defined in the same level `SELECT` clause. – ypercubeᵀᴹ Nov 25 '14 at 13:31