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

Question

I have a table with records and it has a row called category. I have inserted too many articles and I want to select only two articles from each category.

I tried to do something like this:

I created a view:

CREATE VIEW limitrows AS 
   SELECT * FROM tbl_artikujt ORDER BY articleid DESC LIMIT 2 

Then 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?

Solution

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.

Comments

[Alessandro Buscema]

Works perfect, thats what i needed

[Alessandro Buscema]

Can i create view in some way from this select, i tried but it says : 1349 - View's SELECT contains a subquery in the FROM clause

[OMG Ponies]

@AXheladini: Sorry, MySQL won't allow it for a few reasons - the subquery, using variables... MySQL views are extremely restricted, I'm afraid - they list the restrictions in the CREATE VIEW documentation: dev.mysql.com/doc/refman/5.1/en/create-view.html

[Timo Huovinen]

for me this seems to select as top 3 from every category as if it was ordered as t.category ASC, t.articleid ASC but how would you do it to select it as t.category ASC, t.articleid DESC?