On Tue, May 17, 2016 at 12:04 AM, Guyren Howe <guy...@gmail.com> wrote:
> On May 16, 2016, at 20:48 , David G. Johnston <david.g.johns...@gmail.com> > wrote: > > > On Monday, May 16, 2016, Guyren Howe <guy...@gmail.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… > > > 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.
