SQL select N rows from 5 differents id-CodePudding

I got a little question.

I would like to select N rows from 5 id.

I already tried this request :

SELECT a.fk_produit_id, a.id, a.titre, a.prix, a.description, a.short_description, a.image_url 
from produits p, articles a 
WHERE p.id = a.fk_produit_id AND p.fk_category_id IN (2, 248, 335, 493, 1038) 
GROUP BY p.fk_category_id;

Or this one :

SELECT a.fk_produit_id, a.id, a.titre, a.prix 
from articles a 
inner join produits p 
WHERE p.id = a.fk_produit_id AND p.fk_category_id IN (2, 248, 335, 493, 1038) 
order by a.fk_produit_id limit 5

I would like that the result be like this :

id: 1 : -> record 1
          -> record 1
          -> record 1
          -> record 1
          -> record 1
id: 2 : -> record 2
          -> record 2
          -> record 2
          -> record 2
          -> record 2
id: 3 : -> record 3
          -> record 3
          -> record 3
          -> record 3
          -> record 3

id: 4 : -> record 4
          -> record 4
          -> record 4
          -> record 4
          -> record 4
id: 5 : -> record 5
          -> record 5
          -> record 5
          -> record 5
          -> record 5

But they didn't work as i want. If someone coul help me or explain how can I solve it. Thanks you in advance guys I continue my research by my side.

CodePudding user response：

This can be achieved by using ROW_NUMBER()

SELECT  a.fk_produit_id, 
        a.id, 
        a.titre, 
        a.prix, 
        a.description, 
        a.short_description, 
        a.image_url
FROM    (   SELECT  a.fk_produit_id, 
                    a.id, 
                    a.titre, 
                    a.prix, 
                    a.description, 
                    a.short_description, 
                    a.image_url,
                    ROW_NUMBER() OVER(PARTITION BY p.fk_category_id 
                                      ORDER BY a.fk_produit_id) AS RowNumber
            FROM    produits AS p
                    INNER JOIN articles AS a
                        ON p.id = a.fk_produit_id
            WHERE   p.fk_category_id IN (2, 248, 335, 493, 1038)
                
        ) AS a
WHERE   a.RowNumber <= 5; -- Change 5 to whatever "n" is

You may need to alter your ordering as required, but this should get you started

CodePudding user response：

You can use a lateral derived table to return 5 articles per product:

SELECT p.*, a.fk_produit_id, a.id, a.titre, a.prix 
from produits p
cross join
    lateral (select * from articles
             WHERE p.id = fk_produit_id
             order by fk_produit_id
             limit 5) a
where p.fk_category_id IN (2, 248, 335, 493, 1038)