I've been doing a lot of reading on alternatives to the LIMIT clause for SQL SERVER. It's so frustrating that they still refuse to adapt it. Anyway, I really havn't been able to get my head around this. The query I'm trying to convert is this...
SELECT ID, Name, Price, Image FROM Products ORDER BY ID ASC LIMIT $start_from, $items_on_page
Any assistance would be much appreciated, thank you.
In SQL Server 2012, there is support for the ANSI standard OFFSET / FETCH syntax. I blogged about this and here is the official doc (this is an extension to ORDER BY). Your syntax converted for SQL Server 2012 would be:
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;
Prior to that, you need to use various workarounds, including the ROW_NUMBER() method. See this article and the follow-on discussion. If you are not on SQL Server 2012, you can't use standard syntax or MySQL's non-standard LIMIT but you can use a more verbose solution such as:
;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 skin this cat, this is unlikely to be the most efficient but syntax-wise is probably simplest. I suggest reviewing the links I posted as well as the duplicate suggestions noted in the comments to the question.
For SQL Server 2005 and 2008 This is an example query to select rows from 11 to 20 from Report table ordered 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
Try this:
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-
No getting around the subquery, but this is an elegant solution.
Say you wanted 10 items per page, starting on the 5th row, this would give you the bottom 10 rows of the top 14 rows. Essentially LIMIT 5,10
you can use ROW COUNT : Returns the number of rows affected by the last statement. when you don, you reset the rowcont.
SET ROWCOUNT 100
or you can try using TOP query
SELECT TOP 100 * FROM Sometable ORDER BY somecolumn
If you allow the application to store a tad of state, you can do this using just TOP items_on_page. What you do is to store the ID of the last item you retrieved, and for each subsequent query add AND ID > [last ID]. So you get the items in batches of items_on_page, starting where you left off each time.
