Select rows given priority to a column postgresql-CodePudding

I have a table product with id, name and warehouseId. I want to get all products but giving priority to a given warehouseId, i.e. if there is many products with the same name and different warehouseId return only the one with the warehouseId designated, and if the are many products with the same name, different warehouseId and none of the warehouseId matches the designated return any.

Example:

id  | name | warehouseId
-------------------------
 1  |  A   |    1
 2  |  A   |    2
 3  |  B   |    1
 4  |  B   |    3

With designated wharehouseId = 2 expected:

id  | name | warehouseId
-------------------------
 2  |  A   |    2
 3  |  B   |    1

How can achieve that in postgresql 14?

CodePudding user response：

We group by name and put all the wharehouseIds in an array. If the designated wharehouseId is in the array then we choose it, if not we take whatever one is the minimum. Then we join the result with the original table.

with t2 as (
select    name, case when 2 = any(array_agg(warehouseid)) = 't' then 2 else min(warehouseid) end as warehouseid 
from      t 
group  by name
           )

select id, name, warehouseid
from   t join t2 using(name, warehouseid)

id	name	warehouseid
2	A	2
3	B	1

Fiddle

CodePudding user response：

This is a case for DISTINCT ON:

SELECT DISTINCT ON (name)
       id, name, warehouseId
FROM tab
ORDER BY name, warehouseId = 2 DESC;

This relies on the fact that TRUE > FALSE.