Question 90701 in PostgreSQL Question posted in
The official documentation can be found here.

How to select specific items in IN sql – Postgresql

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:

store_id title
1 title a
34 title b
45 title c
100 title d
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search