CODEWITHSUNDEEP
sqlpostgresql
Shivam Batra
Shivam Batra

Reputation: 68

Merge or group rows corresponding to particular column postgresql

I want to group by the resultset further corresponding to the price_type column, if the data for a product with both price_type variant and parent_product is present, then it must show only the variant one

For example, this data

Product Name  PPID   QTY   PRID  PRICE   PRICE_TYPE 
Shorts         1     10     21    200    variant
Shorts         1     10     21    100    parent_product
Night Suit     1     10     22    200    variant
Night Suit     1     10     22    100    parent_product
Denim          1     10     23    400    parent_product

should come like

Product Name  PPID   QTY   PRID  PRICE   PRICE_TYPE 
Shorts         1     10     21    200    variant
Night Suit     1     10     22    200    variant
Denim          1     10     23    400    parent_product

Upvotes: 0

Views: 46

Answers (3)

Sabari
Sabari

Reputation: 244

Below is the simple query to get desired result.

select
  distinct on(product_name),
  t.*
from tab t
order by price_type='variant' desc

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

It seems you want row_number() with conditional ordering:

select *
from (select *, row_number() over (partition by ProductName 
                                     order by (case when Price_type = 'variant' 
                                                    then 0 else 1
                                               end)
                                  ) as seq
       from table
     ) t
where seq = 1;

Upvotes: 1

ikusimakusi
ikusimakusi

Reputation: 159

You can use a window function:

SELECT * FROM 
  (
   SELECT * , 
       RANK() OVER (PARTITION BY product_name ORDER BY priority ) AS rank 
   FROM (
          SELECT *, 
               CASE 
                 WHEN price_type='variant' THEN 1 
                 ELSE 2 
               END AS priority 
          FROM yourtable
        ) AS X
  ) AS Y 
WHERE rank=1

Upvotes: 0

Related Questions