Incorrect syntax near 'LIMIT'

Question

Hi have an SQL statement I need to LIMIT and just cant get the syntax right!

$strSQL = 'SELECT * FROM BlogItem WHERE Blog_Live=1 LIMIT '.$rowsperpage.' OFFSET '.$offset.''; $objQuery = mssql_query($strSQL)
or die ("Error Query [".$strSQL."]");

Can anyone give me some advice?

EDIT:

I am using SQL Server 2008

Tried this:

$strSQL = "SELECT * FROM BlogItem WHERE Blog_Live=1 AND RowNum >= ".$offset." AND RowNum < ".$offset." + ".$rowsperpage."";

Are you sure you're using SQL Server? Because that's MySQL syntax you're using there. If it is SQL Server, this is a duplicate question, and hopefully you can use the newer `OFFSET` and `FETCH` mentioned in one of the answers of the duplicate I've marked.... — Matt Gibson, Dec 15 '15 at 11:12
What are the values of the variables `$rowsperpage` and `$offset`? — Ben, Dec 15 '15 at 11:13
Try this for once. `$strSQL = "SELECT * FROM BlogItem WHERE Blog_Live=1 LIMIT $rowsperpage OFFSET $offset"; $objQuery = mssql_query($strSQL) or die ("Error Query [".$strSQL."]");` — Nana Partykar, Dec 15 '15 at 11:18
Hi Matt, I have read the duplicate but am still not sure how to format my SQL Query, I am using SQL Server 2008! Any advice appreciated! — KiwisTasteGood, Dec 15 '15 at 11:23
`OFFSET` and `FETCH` are only available in SQL Server 2012 onwards, I believe, so you'll have to use one of the more complex older syntaxes using `ROW_NUMBER()` like the accepted answer in that duplicate question. I'd say you should try doing that, and if you have a problem, ask a fresh question with the specifics of the problem you're having. — Matt Gibson, Dec 15 '15 at 11:35
I cant post for another 90 minutes! The ROW_NUMBER() method I don't really understand looking at the example in the answer — KiwisTasteGood, Dec 15 '15 at 11:45

score 0 · Answer 1 · edited Mar 20 '17 at 09:16

did you try like this

SELECT * FROM <table>
    ORDER BY <columns>
    OFFSET <EXPR1> ROWS
    FETCH NEXT <EXPR2> ROWS ONLY

Example :

SELECT * FROM t_book AS P
    ORDER BY P.barcode
    OFFSET 10 ROWS
    FETCH NEXT 20 ROWS ONLY

or

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit`

Incorrect syntax near 'LIMIT'

1 Answers1