locked
Convert MSSQL paging statement to MySQL paging statement RRS feed

  • Question

  • User-1246604461 posted

    This post used to be about stored procedures, but based on suggestion of first commenter I now wish to convert a basic MSSQL statement to MySQL.
    particularly the ROW_NUMBER() and OVER combined with ORDER BY are tricky to me. 

    SELECT * FROM (select ROW_NUMBER() OVER (ORDER BY publishdate DESC) as RowNum,
    * FROM news WHERE publishdate <=getdate()) as info
    WHERE RowNum > 0 AND RowNum <= (100)

    How would I convert that to a MySQL statement?

    Monday, November 11, 2013 5:43 PM

All replies

  • User753101303 posted

    Hi,

    What if you just try to search Google for : execute dynamic statement mysql ?

    Apparently you have EXECUTE IMMEDIATE or PREPARED STATEMENTS (such as http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html )

    Having a stored procedure running a dynamically generated statement kind of defeat the purpose of using stored procedures. My personal preference in this case would be likely to just create the statement client side.

    Friday, November 15, 2013 7:16 AM
  • User-1246604461 posted

    That's a good suggestion. Nog when I'd create the statement clientside I get this:

    SELECT * FROM (select ROW_NUMBER() OVER (ORDER BY publishdate DESC) as RowNum,
    * FROM news WHERE publishdate <=getdate()) as info
    WHERE RowNum > 0 AND RowNum <= (100)

    How would I convert that to a MySQL statement?

    Thanks! 

     

    Sunday, November 17, 2013 3:10 PM