CODEWITHSUNDEEP
sql-server-2008
Pand005
Pand005

Reputation: 1175

How to use limit in Microsoft SQL Server 2008?

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)

Upvotes: 1

Views: 9676

Answers (4)

Pand005
Pand005

Reputation: 1175

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;

Upvotes: -1

entropic
entropic

Reputation: 1683

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

Upvotes: 0

occasionalDev
occasionalDev

Reputation: 84

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

Upvotes: 0

occasionalDev
occasionalDev

Reputation: 84

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

Upvotes: 0

Related Questions