MySql Query: Select top 3 rows from table for each category

by

LIMIT only stops the number of results the statement returns. What you’re looking for is generally called analytic/windowing/ranking functions – which MySQL doesn’t support but you can emulate using variables:

SELECT x.*
  FROM (SELECT t.*,
               CASE 
                 WHEN @category != t.category THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @category := t.category AS var_category
          FROM TBL_ARTIKUJT t
          JOIN (SELECT @rownum := NULL, @category := '') r
      ORDER BY t.category) x
 WHERE x.rank <= 3

If you don’t change SELECT x.*, the result set will include the rank and var_category values – you’ll have to specify the columns you really want if this isn’t the case.

More Related Contents:

  1. Why does MYSQL higher LIMIT offset slow the query down?
  2. Using variable in a LIMIT clause in MySQL
  3. Maximum number of records in a MySQL database table
  4. Can I create view with parameter in MySQL?
  5. Combining UNION and LIMIT operations in MySQL query
  6. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  7. MySQL – UPDATE query with LIMIT
  8. MySQL: View with Subquery in the FROM Clause Limitation
  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 view performance
  13. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  14. Mysql delete statement with limit
  15. UPDATE with ORDER BY and LIMIT not working in MYSQL
  16. MySQL LIMIT on DELETE statement
  17. Should I use the datetime or timestamp data type in MySQL?
  18. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  19. MySQL – Trigger for updating same table after insert
  20. How do I get the current time zone of MySQL?
  21. How to get the difference in years from two different dates?
  22. Writing optional parameters within stored procedures in MySQL? [duplicate]
  23. How to get all columns’ names for all the tables in MySQL?
  24. How do I get MySQL to use an INDEX for view query?
  25. Restore the mysql database from .frm files
  26. Mysql insert random datetime in a given datetime range
  27. MySQL UPDATE and SELECT in one pass
  28. where does MySQL store database files?
  29. MySQL: How to retrieve a random row or multiple random rows?
  30. Should I always prefer MySQL InnoDB over MyISAM?

Leave a Comment