Merge or group rows corresponding to particular column postgresql

Question

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

Yogesh Sharma · Accepted Answer · 2018-06-27 09:38:13Z

1

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;

answered Jun 27, 2018 at 9:38

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sabari · Accepted Answer · 2018-06-27 15:56:33Z

1

Below is the simple query to get desired result.

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

answered Jun 27, 2018 at 15:56

Sabari

2442 silver badges6 bronze badges

Comments

ikusimakusi · Accepted Answer · 2018-06-27 09:38:04Z

0

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

answered Jun 27, 2018 at 9:38

ikusimakusi

1598 bronze badges

Collectives™ on Stack Overflow

Merge or group rows corresponding to particular column postgresql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related