I'm migrating some SQL from PostgreSQL 9.2 to Vertica 7.0, and I could use some help replacing postgres's cool array_agg feature with something that Vertica (and possibly other RDBMS) supports, such as partitions and window functions. I'm new to these features, and I'd really appreciate your ideas.
The (working) query using array_agg ( sql fiddle demo ):
If I try to naively select A.X by itself without the aggregation (i.e., to let the RDBMS pick - actually works with MySQL and SQLite), postgres complains. Running the same query but with "A.X" instead of "(array_agg(A.X))1":
I was thinking of trying a window function, e.g., something like from this question:
but I get the same error:
Note that we don't care so much about getting the first value, we just need any (ideally deterministic) single value.
Thank you in advance.