0

Let's say i have a store with some products in it. so my products are related to different stores. I want to filter 24 products, but max 3 products per store. Basically i can not return more than 3 products from the same store... How can i do that in sql ?

Mcrunny
  • 111
  • 4
  • would be helpful if you will provide data structure/example – Power Mouse Jul 16 '21 at 18:05
  • yeah, let's say i have product = { id: string product_img: string store_img: string store_id: string } so i wanna bring 9 products and up to 3 products with the same store_id foreign key. so if the query brings 5 products with store_id="store-id-1" the final result should show just 3, and then bring another 3 products with a different store_id. – Mcrunny Jul 16 '21 at 18:56
  • check this post: https://stackoverflow.com/questions/176964/select-top-10-records-for-each-category – Power Mouse Jul 16 '21 at 19:14

1 Answers1

0
SELECT * FROM product p1 JOIN LATERAL
    (SELECT TOP 3 id FROM product p2 WHERE p2.store_id = p1.store_id) p3
    ON p3.id = p1.id

Depending on your DBMS (e. g. postgre SQL, mySQL) you might need to use LIMIT instead of TOP:

SELECT id FROM product p2 WHERE p2.store_id = p1.store_id LIMIT 3

Oracle uses yet another syntax...

To get consistent results over multiple requests you might yet apply some ordering especially to the inner query, but outer one as well.

Aconcagua
  • 24,880
  • 4
  • 34
  • 59