Hi, I am working in DB nodes and I am looking for a way to aggregate data and pick FIRST value for a certain criteria.
I have tried using FIRST and TOP 1 in custom aggregation in DB query. I have tried DISTINCT ON within DB Query, but all seems to be not supported. How I can get use FIRST in my data?
Similar case has been posted in thread below.
I am trying to use DB pivot for a table and would like to use manual aggregation method “FIRST” for a column. But it seems like there is no option available for this method with DB pivott
I saw there is an option “custom” to customize aggregation method but not sure how to do so.
Could you please give me some hints for it.
Thank you so much
If you know how to write SQL, use the DB Query Reader. There you can enter the query directly adjusted for whatever database you are using.
It also seems to not recognize FIRST function. I would also like to stay within DB tools, and DB QUERY READER takes the operation outside Impala I am working in.
as far as you didn’t stated which database you use the solution could be different.
If you are connected to an oracle database, then read this description FIRST_VALUE (oracle.com).
Thank you morpheus. With you advice i was able to come up with syntax below. As far as I know I am working with Apache Impala.
FIRST_VALUE (billing_type) OVER (partition by sales_order order by `vol_sold_sku_qty` DESC) as billing_type,
FIRST_VALUE (delivery_date) OVER (partition by sales_order order by `vol_sold_sku_qty` DESC) as delivery_date