Postgres: WHERE IN subselect with multiple columns?

Question

I'm new to Postgres -- and it's been years since I've done anything SQL related so I'm perhaps over-thinking this -- but...

I have a subquery which selects a bunch of IDs (cs_seed) to perform another query on.

What I'd like to be able to do is to maintain the order from the subquery. I've searched for hours and discovered the row_number() feature which seems promising, but I obviously can't use this in a WHERE IN query as it's returning multiple columns.

SELECT ca_seed, ca_biome, ca_percent 
FROM colours_area 
WHERE ca_seed IN (SELECT cs_seed, row_number() OVER (ORDER BY cs_percent DESC) AS rn 
                  FROM colours_spawn 
                  WHERE cs_biome = 140 
                  ORDER BY cs_percent DESC LIMIT 10) 
ORDER BY rn DESC;

Is there any way I can do this? Or is my approach wrong?

IN () select between parentheses must return only one column — McNets
– McNets, Commented Feb 19, 2017 at 22:57

Gordon Linoff · Accepted Answer · 2017-02-19 22:59:27Z

1

Use join?

SELECT ca.ca_seed, ca.ca_biome, ca.ca_percent 
FROM colours_area ca JOIN
     (SELECT cs_seed, row_number() OVER (ORDER BY cs_percent DESC) AS rn 
      FROM colours_spawn 
      WHERE cs_biome = 140 
      ORDER BY cs_percent DESC
      LIMIT 10
     ) cs
     ON ca.ca_seed = cs.cs_seed
ORDER BY rn DESC;

This assumes that cs_seed only appears once in the subquery. Otherwise, you might have to do more manipulation to deal with duplicates.

answered Feb 19, 2017 at 22:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Lee Davies Over a year ago

That's something similar to what I originally tried, but it's not producing the results that I know are correct when applying a WHERE ca_seed IN () clause to the main query. That's why I resorted to using the subquery to return the list of cs_seeds to then get the matching entries from the colours_area table. Duplication of results doesn't bother me, due to the relationships.

Collectives™ on Stack Overflow

Postgres: WHERE IN subselect with multiple columns?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related