The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
array_agg in vertica

Array_agg is something supported by Postgres, but not Vertica.
What would be the best way to achieve the following?
SELECT id, business_time_minute, (array_agg(price ORDER BY business_time_second DESC))[1] AS minute_price, FROM ticks GROUP BY business_time_minute
Unfortunately, as far as I tried, last_value or ts_last_value would work with group by, otherwise it would be perfect.
Thanks
0
Answers
Take a peek at LISTAGG.
Thanks, Jim, how would you sort the list so that I can get the first or last ordered by c0, c1 below?
I want to get if first:
or last:
to get the first ordered by c0,c1 you can use the below query
dbadmin=> select * from some_data order by c1 asc,c0 asc;
c0 | c1 | c2
----+----+----
1 | 1 | C
2 | 1 | B
1 | 2 | A
1 | 3 | D
1 | 4 | Z
(5 rows)
these are the results I want, not the full set. array_agg allows for order by, I wonder how to do that with listagg.
```
c0 | c1 | c2
----+----
1 | 1 | C
2 | 1 | B
c0 | c1 | c2
----+----
1 | 4 | Z
2 | 1 | B
```
@BHIFP - I think this what you mean ...
Unfortunately you have to do that LIMIT trick for now... See: Ordering the List of Values Returned from LISTAGG
The good news is that there is an ORDER BY feature coming to the LISTAGG function real soon! I will update this thread when it's avaible and whch version of Vertica it's in!