Select number of rows from each category

Question

I have messages table: Messages:

Id, Content, Category, createdAt 

1, test1, cat1, 2018-03-26 18:22:39  
2, test2, cat1, 2018-03-26 18:22:46 
3, test3, cat2, 2018-03-26 18:22:52 
4, test4, cat2, 2018-03-26 18:23:11  
5, test5, cat2, 2018-03-26 18:23:13
6, test6, cat1, 2018-03-26 18:23:17

From this data, I want to select 2 rows of each category i.e. Cat1 & Cat2 with 2 rows each.

So what will be the SQL query to retrieve this data? This is sample data and like this, I have thousands of rows and 20 to 25 different categories. So I want to retrieve the same number of rows from each category. Also, this rows should be in ascending order according to a createdAt column.

Expected Output(Assuming createdAt is latest of bottom rows):

Id, Content, Category, createdAt 

6, test6, cat1, 2018-03-26 18:23:17  
2, test2, cat1, 2018-03-26 18:22:46  
5, test5, cat2, 2018-03-26 18:23:13
4, test4, cat2, 2018-03-26 18:23:11

Specify the expected result. And show us your current query attempt. — jarlh, Mar 27 '18 at 06:09
I have added expected output in Question.. Group by category returning only single row for each category. So nested query might be needed in this scenario — shivaji bhosale, Mar 27 '18 at 06:14
Your table header has 4 columns, but table data has only 3 columns. createdAt seems to be missing. Is that column important? — jarlh, Mar 27 '18 at 06:17
@Strawberry 2 in the scense for this example i want to but in real i want 200 from each category.. — shivaji bhosale, Mar 27 '18 at 06:45

score 3 · Accepted Answer · edited Mar 27 '18 at 07:19

3

Using Dynamic Top In place of N you can use any number

SELECT Id,Content,Category,createdAt
  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 Table1 t
          JOIN (SELECT @rownum := NULL, @category := '') r
      ORDER BY t.Category,t.createdAt DESC) x
 WHERE x.rank <= 'N'

In place of N you can use any number

Output

ID  Content Category    createdAt
6   test6   cat1    2018-03-26T18:23:17Z
2   test2   cat1    2018-03-26T18:22:46Z
5   test5   cat2    2018-03-26T18:23:13Z
4   test4   cat2    2018-03-26T18:23:11Z

Live Demo

http://sqlfiddle.com/#!9/00ca02/20

edited Mar 27 '18 at 07:19

Strawberry

33,750
13
40
57

answered Mar 27 '18 at 06:27

Jay Shankar Gupta

5,918
1
10
27

Again this is returning single row from each category. I need 2 rows from each category. – shivaji bhosale Mar 27 '18 at 06:30
Check Now I Edited My Answer – Jay Shankar Gupta Mar 27 '18 at 06:43
Its working for 2 rows. But in real i want 200 rows from each category, So is it possible to generalize this query to specify the n number of records from each category? – shivaji bhosale Mar 27 '18 at 06:48
Check now I added generalize answer also – Jay Shankar Gupta Mar 27 '18 at 06:54

score 1 · Answer 2 · answered Mar 27 '18 at 06:53

Since there is no row_Number() function is MySql it becomes a little bit more tricky.

select 
  a.Id, a.Content, a.Category, a.createdAt
from Table1 a
JOIN Table1 b ON a.Category = b.Category AND a.createdAt <= b.createdAt
GROUP BY a.Category, a.createdAt
having count(*) <= 2
order by Category, CreatedAt desc

score 0 · Answer 3 · answered Mar 27 '18 at 06:45

I guess that rank is what you are looking for

SELECT
  (
    CASE category
    WHEN @curCat
      THEN @curRow := @curRow + 1
    ELSE @curRow := 1 AND @curCat := category END
  ) + 1 AS rank,
  category,
  id,
  content
FROM tab1,
  (SELECT
     @curRow := 0,
     @curType := '') r
ORDER BY category
         AND rank <= 2;

Select number of rows from each category

3 Answers3