Window Partition in Postgres with a varchar

Question

lets say I had the following two columns (brand & color) the values are:

brand   color
ford    blue
ford    pink
ford    yellow
chevy   green
chevy   white
chevy   yellow
jeep    blue
jeep    green

I only care about the brands that sell a pink OR blue car. what type of window function would allow me to only pull back the THREE 'ford' records because ford has a pink / blue car. And the TWO jeep records. (I don't want to see any chevy records). thanks

cachique · Accepted Answer · 2016-07-28 23:24:54Z

1

You should do a distinct query on the brand column with the color condition. you will ge the list of brands which are viable for querying. then join this result with the normal table based on the brand. and then get the record.

in other words you need a subquery with the distinct brands and the condition which is inner joined to your normal table

something like

select t1.* 
from t1 
inner join (
  select distinct brand
  from t1 
  where color in ('pink', 'blue') 
  ) as t2 on t2.brand = t1.brand

edited Jul 28, 2016 at 23:24

cachique

1,2811 gold badge13 silver badges19 bronze badges

answered Jul 28, 2016 at 19:40

Miad Abrin

9587 silver badges14 bronze badges

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

Comments

Slava Lenskyy · Accepted Answer · 2016-07-29 07:05:23Z

0

SELECT *
FROM t1
WHERE brand in (
                 SELECT brand
                 FROM t1
                 WHERE color in ('pink', 'blue') 
               )

Read this query in English: select all records from table where brand equals to all brands with color pink or blue.

answered Jul 29, 2016 at 7:05

Slava Lenskyy

4662 silver badges11 bronze badges

Collectives™ on Stack Overflow

Window Partition in Postgres with a varchar

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related