Re: FIRST_VALUE argument must appear in group by?

On Monday, May 16, 2016, Guyren Howe <guyren(at)gmail(dot)com> wrote:

> I have this SELECT clause as part of a larger query:
>
> FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position ASC) AS current_drs_id
>
> Seems reasonable to me: group and sort the fields in this table and give
> me the first value.
>
> But I get "column "drs.id" must appear in the GROUP BY clause or be used
> in an aggregate function".
>
> Huh?
>

The larger query would help...

First_value as shown is in a window expression. It's surrounding query
must also have a group by on it. Fields in a windowed function are
not considered aggregated for purposes of group by.

Select day_of_month, sum(sales), sum(sum(sales)) over ()
From ...
Group by day_of_month;

For each day of month give me total sales. I also want to see the total
sales over the whole query. I need to sum together the individual daily
sums. You seem to have a similar situation in your query. The reasoning
of first_value is not easy to speculate upon though.

David J.