Re: FIRST_VALUE argument must appear in group by?

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

On Tue, May 17, 2016 at 12:04 AM, Guyren Howe <guyren@xxxxxxxxx> wrote:

On May 16, 2016, at 20:48 , David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

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…

SELECT
  o.id,
  os.status AS status,
  o.status_updated_at,
  o.should_hold_at_airlines,
  (SELECT drs2.id FROM delivery_route_segments drs2 WHERE drs2.order_id = o.id AND NOT drs2.completed ORDER BY drs2.position LIMIT 1) AS current_drs_id,


FROM
  orders o JOIN
  order_statuses os ON (o.status = os.id) JOIN
  delivery_route_segments drs ON (drs.order_id = o.id) JOIN
  pick_up_addresses pua ON (pua.order_id = o.id)
GROUP BY
  o.id, os.status

I would prefer to do the subquery as a window function, both because that is cleaner to read and also because I believe it is likely to be more efficient.

​[reading a bit more closely now...]​

​IMO opinion you are abusing GROUP BY here - since you don't seem to care about aggregation but rather are compensating for the presence of additional joins and their propensity of introducing additional undesirable rows.

Judicious use of subqueries, semi-joins (i.e., EXISTS clause), and maybe - if all else fails - DISTINCT, would be better than throwing window functions into the mix.

David J.