Using variable in a LIMIT clause in MySQL

by

For those, who cannot use MySQL 5.5.6+ and don’t want to write a stored procedure, there is another variant. We can add where clause on a subselect with ROWNUM.

SET @limit = 10;
SELECT * FROM (
  SELECT instances.*, 
         @rownum := @rownum + 1 AS rank
    FROM instances, 
         (SELECT @rownum := 0) r
) d WHERE rank < @limit;

More Related Contents:

  1. MySQL: @variable vs. variable. What’s the difference?
  2. Why does MYSQL higher LIMIT offset slow the query down?
  3. Create table variable in MySQL
  4. Maximum number of records in a MySQL database table
  5. Combining UNION and LIMIT operations in MySQL query
  6. How can I simulate an array variable in MySQL?
  7. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  8. MySQL – UPDATE query with LIMIT
  9. How to set a maximum execution time for a mysql query?
  10. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  11. MySQL JOIN with LIMIT 1 on joined table
  12. MySql Query: Select top 3 rows from table for each category
  13. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  14. Mysql delete statement with limit
  15. How to store Query Result in variable using mysql
  16. UPDATE with ORDER BY and LIMIT not working in MYSQL
  17. MySQL LIMIT on DELETE statement
  18. How to pass a variable to a IN clause?
  19. INSERT INTO SELECT statement with more columns which is not in another table
  20. Normalization in MYSQL
  21. MySQL load NULL values from CSV data
  22. How to recover MySQL database from .myd, .myi, .frm files
  23. How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?
  24. Duplicating a MySQL table, indices, and data
  25. How to export data from SQL Server 2005 to MySQL [closed]
  26. “where 1=1” statement [duplicate]
  27. mysql select sum group by date
  28. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  29. MySQL Integer vs DateTime index
  30. MySQL errorno 121

Leave a Comment