CODEWITHSUNDEEP
sqlpostgresql
Whimusical
Whimusical

Reputation: 6629

Top result after ordering within a CALCULATED group by, in postgres, without nesting queries (subqueries, ctes...) in 2020?

I tried with WINDOW functions, Ordered Sets, Hyptohetical Sets, DISTINCT ON, Grouping Sets and I don't find the way to do this simple, without having to nest queries or using CTE precalculations... but I feel like it might exist

I just want to do something like this:

SELECT FIRST_VALUE(my_computed_field ORDER BY order_field) -- i.e, after the virtual grouping, output as the aggregation the internal row that comes first if ordering by another field that does not take part in the group by calculation
FROM mytable 
GROUP BY ABSOLUTELY_MONSTRUOUS_CALCULATION(my_computed_field)

without having to define the ABSOLUTELY_MONSTRUOUS_CALCULATION(mycomputedfield) part twice

Upvotes: 1

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Is this what you want?

SELECT DISTINCT ON (amc) ABSOLUTELY_MONSTRUOUS_CALCULATION(my_computed_field) as amc, my_computed_field
FROM mytable 
ORDER BY amc, order_field;

You can also do this as:

SELECT DISTINCT ON (v.amc) my_computed_field
FROM mytable t CROSS JOIN LATERAL
     (VALUES (ABSOLUTELY_MONSTRUOUS_CALCULATION(my_computed_field) )) v(amc)
ORDER BY v.amc, order_field;

Upvotes: 1

Related Questions