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

Question

I have an table with records and this table has an row called category. I have inserted to many articles and i want to select only to articles from each category.

I tried to do something like this: i created an view: create view limitrows as select * from tbl_artikujt order by articleid desc limit 2

and than i created this query:SELECT * FROM tbl_artikujt where artikullid IN (select artikullid from limitrows order by category DESC) order by category DESC;

But this is not working and is giving me only two records ?

Answer 1

Read this article: Top N of a group

Answer 2

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.