I have a table "products" with a column called "store_id". This table has a lot af products from many stores. I need to select 4 random products from 4 specific stores (id: 1, 34, 45, 100). How can I do that?

I've tried to like this:

SELECT * FROM products WHERE store_id IN (1, 34, 45, 100)

But that query returns duplicated records (by store_id). I need the following result:

CodePudding user response：

Use the DISTINCT construct to get unique records for the desired column:

SELECT distinct on (store_id) store_id, title FROM products WHERE store_id IN (1, 34, 45, 100);

Demo in sqldaddy.io

CodePudding user response：

To get a true random pick of the products use a row_number function with random order.

This query shows all data with a random index of the product for each store

select products.*,
row_number() over (partition by store_id order by random()) rn
from products
where store_id in (1,34)

store_id|product_id|title|rn|
-------- ---------- ----- -- 
       1|         1|a    | 1|
       1|         3|c    | 2|
       1|         2|b    | 3|
      34|         6|f    | 1|
      34|         7|g    | 2|
      34|         8|h    | 3|
      34|         5|e    | 4|
      34|         4|d    | 5|

To get only one product per store simple filter with rn=1

with prod as (
select products.*,
row_number() over (partition by store_id order by random()) rn
from products
where store_id in (1,34)
)
select store_id, title from prod
where rn = 1
;

store_id|title|
-------- ----- 
       1|a    |
      34|e    |

Note this query will produce a different result on each run. If you need a stability you must call setseed before each execution. E.g.

SELECT setseed(1)