Re: FIRST_VALUE argument must appear in group by?

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Monday, May 16, 2016, Guyren Howe <guyren@xxxxxxxxx> 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.