CODEWITHSUNDEEP
sqlpostgresql
khajlk
khajlk

Reputation: 861

ERROR in PostgreSQL: Subquery has too many columns

I have a table my_table in my PostgreSQL database containing three columns like:

gid id  max_height
3   1   19.3
3   2   19.3
3   3   20.3
3   4   20.3
3   5   19.3
3   6   19.3
3   7   21.4
3   8   21.4
3   9   21.4
3   10  21.4
3   11  21.4
3   12  21.4
22  1   23.1
22  2   23.1
22  3   23.1
22  4   23.1
22  5   23.1
22  6   23.1
22  7   22.1
22  8   22.1
22  9   22.1
22  10  22.1
22  11  22.1
22  12  22.1
29  1   24
29  2   24
29  3   24
29  4   18.9
29  5   18.9
29  6   18.9
29  7   NULL
29  8   NULL
29  9   27.1
29  10  27.1
29  11  6.5
29  12  6.5

For each gid group, there are 12 values (id and max_height). I am trying to select max_height from my_table and trying to compare with the ones in sub-query. The code is:

SELECT
gid,
max_height
FROM
my_table
where max_height not in
(
SELECT
gid, max_height
FROM
-- this part selects the most repeated max_height from my_table
(
SELECT gid, max_height,
ROW_NUMBER() OVER (PARTITION BY gid ORDER BY freq DESC) AS rn
    FROM (
        SELECT gid, max_height, COUNT(id) AS freq
        FROM my_table
        GROUP BY 1, 2
        )hgt_freq
) ranked_hgt_req
WHERE rn = 1
)

I am getting

ERROR: subquery has too many columns at Where max_height NOT IN

Can anyone help me to make me understand what mistake I am making or could help me to resolve the error?

Upvotes: 1

Views: 6990

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

  SELECT
    gid,
    max_height
    FROM
    my_table
    where max_height not in
    (
    SELECT
      max_height  /* you must select only a column do the fact in where you have  a column */
    FROM
    -- this part selects the most repeated max_height from my_table
    (
    SELECT gid, max_height,
    ROW_NUMBER() OVER (PARTITION BY gid ORDER BY freq DESC) AS rn
        FROM (
            SELECT gid, max_height, COUNT(id) AS freq
            FROM my_table
            GROUP BY 1, 2
            )hgt_freq
    ) ranked_hgt_req
    WHERE rn = 1
    )

Upvotes: 4

Related Questions