patternsqlMinor
Aggregate function that picks any/first/last/random value
Viewed 0 times
randomlastanyfunctionvaluefirstthataggregatepicks
Problem
I run a query which looks like below
I have knowledge that
WITH src AS (
...
)
SELECT MAX(field1), array_agg(field2)
FROM srcI have knowledge that
field1 is the same across all rows returned from src so I really don't really care whether the aggregate function used with it is MAX or MIN. In my case field1 is an array so I am curious if there is a more efficient way to grab any value of field1 which doesn't include overhead of comparing it with the previously saved MIN/MAX. If there was a function to grab the first / retain the last value of field1 that would be ideal.Solution
I believe I found a more efficient SQL for what I am trying to achieve
Sub-select on
I am not marking it as an answer for now 'cause someone may still suggest a more efficient way.
WITH src AS (
...
)
, identical_field1 (
SELECT field1 FROM src LIMIT 1
)
SELECT (SELECT field1 FROM identical_field1), array_agg(field2)
FROM srcSub-select on
field1 is evaluated only once and only requests one single row from src.I am not marking it as an answer for now 'cause someone may still suggest a more efficient way.
Code Snippets
WITH src AS (
...
)
, identical_field1 (
SELECT field1 FROM src LIMIT 1
)
SELECT (SELECT field1 FROM identical_field1), array_agg(field2)
FROM srcContext
StackExchange Database Administrators Q#188230, answer score: 5
Revisions (0)
No revisions yet.