1

We have to use Limit keyword in SQL server 2008/2012.

We need to apply limit for every query where start index will change every time. When I was googling found TOP but it won't work for us. Can anyone please share how to use LIMIT keyword in sql server where every time start index change.

We need query in SQL server like below -

SELECT * from STOCK LIMIT 11, 10000 (where 11=start index, 10000=size)
T I
  • 9,785
  • 4
  • 29
  • 51
Pand005
  • 1,095
  • 3
  • 23
  • 53
  • 2
    There is no `LIMIT` in SQL Server. So you can't use it. What exactly is "*TOP won't work for us*" supposed to mean? –  Jul 30 '14 at 15:17
  • 1
    If 2012 you can use [offset-fetch](http://stevestedman.com/2012/04/tsql-2012-offset-and-fetch/) – T I Jul 30 '14 at 15:17
  • `LIMIT` is a MySQL/Postgres-specific extension which isn't generally available in all SQL databases (and it's not part of the SQL language standard, either). – marc_s Jul 30 '14 at 15:20
  • possible duplicate of [MySQL LIMIT clause equivalent for SQL SERVER](http://stackoverflow.com/questions/9013177/mysql-limit-clause-equivalent-for-sql-server) – usr Jul 30 '14 at 15:25
  • With the help of offset and fetch we could resolve the issue of LIMIT in SQL server 2012. Example - SELECT ITEM_ID, PRICE FROM MENU ORDER BY ITEM_ID ASC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; – Pand005 Jul 31 '14 at 06:29

4 Answers4

0

This is probably not a good long-term solution for you, but if the table has an Identity field you could do something like this:

SELECT TOP 1000 * FROM STOCK
WHERE Id > 11
ORDER BY Id
occasionalDev
  • 84
  • 5
0

You could use a CTE with a window function, like in the answer to this SO question -> Skip first row in SQL Server 2005?

Community
  • 1
  • 1
occasionalDev
  • 84
  • 5
0

You would have to use something like the ROW_NUMBER() function and then specify what you want from there.

select top 10000 *, row_number() over (order by [YourFieldName]) as row from Stock where row > 11
entropic
  • 1,683
  • 14
  • 27
  • With the help of offset and fetch we could resolve the issue of LIMIT in SQL server 2012. Example - SELECT ITEM_ID, PRICE FROM MENU ORDER BY ITEM_ID ASC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; – Pand005 Jul 31 '14 at 06:30
-1

We have upgraded to SQL server 2012 and replaced query with OFFSET and FETCH. Sample is below.

SELECT ITEM_ID, PRICE FROM MENU ORDER BY ITEM_ID ASC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Pand005
  • 1,095
  • 3
  • 23
  • 53