Search code examples
sqlpostgresqlselectsubquerydistinct-values

How to create unique id entries and filter for specific column values for these ids (Postgresql)

Below you can see for each customer several product entries and the time they bought the product. However I am only intersted whether they ever bought product 1, and if they have never bought it I would fill the cell with product x instead of skipping the id. I am using Postgresql!

This is the current table:

product

and this is the desired outcome:

product2

I am trying this table but it does not provide quite what I am looking for. Is it possible to transform the first table into the 2nd at all?

select distinct on ("id")
"id", "buying date", "product",
case when "product"='product 1' then 'product 1' else 'product x' as "Product2"
from product
order by 1,2
Solution

  • If I understand correctly, the logic you want should be in the order by clause:

    select distinct on ("id")
       "id", "buying date", "product"
from product
order by id,
         ("product" = 'product 1') desc,  -- put product1 first
         "buying date";

    For a boolean expression, DESC orders the true value first then the false.