Search Postgresql Archives

Re: FIRST_VALUE argument must appear in group by?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux