SQL N items per Group in SQL || SQL Sub Query Limit

I want to Select join with cats table with data. Join with cats.id = data.cat_id. with limit 3 data table row for each unique category id cat.id

Table: cats

id | parent_id | name
---+-----------+--------
1  | 0         | Games
2  | 0         | Music
3  | 0         | Apps

Table:data

--------------------------
id |  cat_id |  data
---+---------+------------
1  |  1      |  Cat1 Data1
2  |  1      |  Cat1 Data2
3  |  1      |  Cat1 Data3
4  |  1      |  Cat1 Data4
---+---------+------------
5  |  2      |  Cat2 Data1
6  |  2      |  Cat2 Data2
7  |  2      |  Cat2 Data3
8  |  2      |  Cat3 Data4
---+---------+------------
9  |  3      |  Cat3 Data1
10 |  3      |  Cat3 Data2
11 |  3      |  Cat3 Data3
12 |  3      |  Cat3 Data4

I want like bellow. For one cats per row select three data row select.

category  | data
----------+------------
Games     | Cat1 Data1
Games     | Cat1 Data2
Games     | Cat1 Data3
----------+------------
Music     | Cat2 Data1
Music     | Cat2 Data2
Music     | Cat2 Data3
----------+------------
Apps      | Cat3 Data1
Apps      | Cat3 Data2
Apps      | Cat3 Data3

if this not posiable group_concat with , also work for me. I will split it with php.

cat_name  |  data
----------+-------------------------------------
Book      | Cat1 Data1, Cat1 Data2, Cat1 Data3
Music     | Cat2 Data1, Cat2 Data2, Cat2 Data3
Game      | Cat3 Data1, Cat3 Data2, Cat3 Data3

Answer

Hope this solve your

SELECT
    sq.category,
    sq.data
FROM (
    SELECT
        DATA.*,
        cats.name AS category,
        row_number() over(PARTITION BY cat_id ORDER BY cat_id DESC) AS ROW_COUNT FROM `data`
    INNER JOIN cats ON data.cat_id = cats.id
    ) AS sq
WHERE
    ROW_COUNT <= 3

here in the query … … ROW_COUNT <= 3 you may set limit for selection.

Advertisement

Answer