Organizational Research By

Surprising Reserch Topic

mysql query select top 3 rows from table for each category


mysql query select top 3 rows from table for each category  using -'mysql,view,limit'

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?
    

asked Sep 16, 2015 by sujata naik
0 votes
10 views



Related Hot Questions

1 Answer

0 votes

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.

answered Sep 16, 2015 by rajeevr642

...