MySQL LIMIT clause equivalent to SQL SERVER

Question

I read a lot about alternatives to the LIMIT clause for SQL SERVER. It is so frustrating that they still refuse to adapt it. Anyway, I really couldn't think it over. The query I'm trying to convert is ...

SELECT ID, Name, Price, Image FROM Products ORDER BY ID ASC LIMIT $start_from, $items_on_page 

Any help would be greatly appreciated, thanks.

Answer 1

SQL Server 2012 supports the ANSI OFFSET / FETCH standard. I reported this and here is an official document (this is an extension to ORDER BY ). Your syntax converted for SQL Server 2012 will look like this:

 SELECT ID, Name, Price, Image FROM Products ORDER BY ID ASC OFFSET (@start_from - 1) ROWS -- not sure if you need -1 -- because I don't know how you calculated @start_from FETCH NEXT @items_on_page ROWS ONLY; 

Before that, you need to use various workarounds, including the ROW_NUMBER() method. See this article and discussion. If you are not using SQL Server 2012, you cannot use the standard syntax or MySQL non-standard LIMIT , but you can use a more detailed solution, for example:

 ;WITH o AS ( SELECT TOP ((@start_from - 1) + @items_on_page) -- again, not sure if you need -1 because I -- don't know how you calculated @start_from RowNum = ROW_NUMBER() OVER (ORDER BY ID ASC) /* , other columns */ FROM Products ) SELECT RowNum /* , other columns */ FROM o WHERE RowNum >= @start_from ORDER BY RowNum; 

There are many other ways to trick this cat, it is unlikely to be the most efficient, but the syntax is probably the easiest. I suggest considering the links that I posted, as well as the duplicate suggestions noted in the comments to the question.

Answer 2

For SQL Server 2005 and 2008 This is an example query to select rows 11 to 20 from a report table sorted by LastName.

 SELECT a.* FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY LastName) as row FROM Report) a WHERE a.row > 10 and a.row <= 20 

Answer 3

Try the following:

 SELECT TOP $items_on_page ID, Name, Price, Image FROM (SELECT TOP $start_from + $items_on_page - 1 * FROM Products ORDER BY ID) as T ORDER BY ID DESC 

EDIT: Explanation -

Do not bypass the subquery, but this is an elegant solution. Suppose you need 10 elements per page, starting at line 5 th, this will give you the bottom 10 lines in the top 14 lines. Essentially LIMIT 5,10

Answer 4

you can use ROW COUNT: Returns the number of rows affected by the last statement. when you click, you reset rowcont.

 SET ROWCOUNT 100 

or you can try using the TOP query

 SELECT TOP 100 * FROM Sometable ORDER BY somecolumn 

Answer 5

If you allow the application to save multiple states, you can do this using only TOP items_on_page . What you are doing is storing the identifier of the last element you selected and for each subsequent request add AND ID > [last ID] . This way you get lots in items_on_page starting from where you stopped each time.