GROUP BY custom aggregate

Question

I'm trying to group by subquery via an aggregate below (I simplified the query to its most basic form, a group by n query).

But instead of using a max, I now need the following heuristic:

bar ranges from 0-2. I want to choose from 2,0,1 in that order. But I then want to original join on the original value of bar.

Can I write a custom aggregate function to return the right property? I'm a little lost on how the two would be combined.

SELECT 
    FOO.bar2
FROM
   FOO
INNER JOIN(
    SELECT
        FOO.id,
        custom_aggrgate(bar)
    FROM
        FOO                    
    GROUP BY
        FOO.id            
    ) b ON FOO.bar = inverse_of_custom_aggrgate(bar) -- get org. value of bar to join by

Can you provide an example of input values and the output you are looking for. — Alex, May 29 '16 at 07:22

score 1 · Accepted Answer · edited May 23 '17 at 11:44

Simplest solution would be with DISTINCT ON and a CASE expression in ORDER BY:

I assume this is what you actually want:

SELECT DISTINCT ON (id) bar2
FROM   foo
ORDER  BY id, CASE bar WHEN 2 THEN -1 ELSE bar END;

No need for custom aggregate function.
No need for another join.
No need for a function to invert the result of said custom aggregate function.

About DISTINCT ON:

Select first row in each GROUP BY group?

The query in the question is syntactically invalid and probably not what you want. With the syntax fixed, it might look like this:

SELECT FOO.bar2
FROM   FOO
INNER  JOIN (
    SELECT FOO.id, custom_aggrgate(bar) AS bar
    FROM   FOO                    
    GROUP  BY FOO.id            
    ) b ON FOO.bar = inverse_of_custom_aggrgate(b.bar);

But that's probably still nonsense. If bar isn't defined unique (which doesn't seem likely in your scenario), you get many duplicate rows. An equivalent for this dubious query would be:

SELECT f.bar2
FROM   foo f
JOIN  (
   SELECT DISTINCT ON (id) bar
   FROM   foo
   ORDER  BY id, CASE bar WHEN 2 THEN -1 ELSE bar END
   ) b USING (bar);

I don't expect that's what you need.

GROUP BY custom aggregate

1 Answers1