How to have 2 columns in a subquery but ignore one of them?

Question

I'm trying to execute this query on my database which is querying 2 tables based on the results of a third table.

SELECT *
FROM   ads_user AS u
       INNER JOIN ads_medium AS m
               ON u.id = m.owner_id
WHERE m.id IN (SELECT medium_id,
                                  Count(*) AS count
                           FROM   ads_click
                           WHERE  time > '2017-01-01'
                           GROUP  BY medium_id
                           ORDER  BY count DESC
                           LIMIT  100);

As you can see I'm using two columns in my subquery which the count column is necessary for the whole query to work. Unfortunately PostgreSql throws the subquery has too many columns error.

Is there any workaround for this?

score 1 · Accepted Answer · answered Aug 26 '17 at 06:02

Simply remove the count as a selected column and add (*) to the order by. Thus:

SELECT *
FROM   ads_user AS u
       INNER JOIN ads_medium AS m
               ON u.id = m.owner_id
WHERE m.id IN (SELECT medium_id
                           FROM   ads_click
                           WHERE  time > '2017-01-01'
                           GROUP  BY medium_id
                           ORDER  BY count(*) DESC
                           LIMIT  100);

Postgres complained because your IN referred to two columns, whereas it should only be one. It is perfectly OK to have the Count(*) in the order by.

How to have 2 columns in a subquery but ignore one of them?

1 Answers1