Is there a way for me to find the max of a string per window value

Question

I have a table where I want to find the max value of an array per distinct values in column. I've been trying to use a window function but it says i need to use group by on my unnested value.

    listed_in_city     | rest_type
-----------------------+-----------
 Koramangala 5th Block | {Cafe}
 Koramangala 5th Block | {Cafe}
 Koramangala 5th Block | {Cafe}
 Koramangala 5th Block | {Cafe}
 Koramangala 5th Block | {Cafe}
(5 rows)

i tried using window function like this:

select listed_in_city, max(element) OVER(PARTITION)
FROM (
    select unnest(rest_type) as element, listed_in_city FROM zomato
) a
group by listed_in_city
;

but gives me error:

ERROR:  column "a.element" must appear in the GROUP BY clause or be used in an aggregate function

Thank you

Not sure you need a window function here. Try this : `SELECT listed_in_city, max(element) FROM zomato CROSS JOIN LATERAL unnest(rest_type) AS element GROUP BY listed_in_city` — Edouard, Dec 25 '22 at 16:59

jian · Answer 1 · 2022-12-26T04:58:07.797

WITH cte (
    listed_in_city
    , rest_type
) AS (
    VALUES ('Koramangala 5th Block'
            , '{Cafe,value_a}'::text[])
        , ('Koramangala 5th Block'
            , '{Cafe,value_b}'::text[]))
SELECT
    listed_in_city
    , max(elem) OVER (PARTITION BY listed_in_city)
FROM
    cte
    , unnest(rest_type) a (elem);

it will generated 4 rows (listed_in_city * sum(number of scalar elements in the text array). So it will lots of duplicate rows. So maybe just group by is what you want.

WITH cte (
    listed_in_city,
    rest_type
) AS (
    VALUES ('Koramangala 5th Block', '{Cafe,value_a}'::text[]),
        ('Koramangala 5th Block', '{Cafe,value_b}'::text[]))
SELECT
    listed_in_city,
    max(elem)
FROM
    cte,
    unnest(rest_type)
    WITH ORDINALITY AS a (elem, nr)
GROUP BY
    1;

PostgreSQL unnest() with element number

Is there a way for me to find the max of a string per window value

1 Answers1