Обсуждение: FIRST_VALUE argument must appear in group by?
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?
On Monday, May 16, 2016, Guyren Howe <guyren@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_idSeems 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.
On May 16, 2016, at 20:48 , David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, May 16, 2016, Guyren Howe <guyren@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_idSeems 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.
On May 16, 2016, at 20:48 , David G. Johnston <david.g.johnston@gmail.com> wrote:On Monday, May 16, 2016, Guyren Howe <guyren@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_idSeems 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.statusI 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.
This query is non-functional. It has a GROUP BY without any aggregate functions and not all of the selected columns are in the group by.
David J.
I am trying to use a window function, but it's not working. The subquery is effectively aggregating.
On May 17, 2016, at 6:18 , David G. Johnston <david.g.johnston@gmail.com> wrote:On May 16, 2016, at 20:48 , David G. Johnston <david.g.johnston@gmail.com> wrote:On Monday, May 16, 2016, Guyren Howe <guyren@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_idSeems 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.statusI 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.This query is non-functional. It has a GROUP BY without any aggregate functions and not all of the selected columns are in the group by.David J.
I am trying to use a window function, but it's not working. The subquery is effectively aggregating.
I would advise avoiding using select-list scalar (and particularly correlated) subqueries when the query level in question also contains a group by. Do the group by in a subquery and then in the parent query pull in whatever subselects you want to execute.
You need to make it easier for people to help. The first post you sent was just a window function. I asked for
detail and you sent a query without one. Given that discrepancy, added to the fact the query is question is not self-contained, I am less than inclined to try and figure out what you are trying to do.
Given me a query that has a window function and looks like, if said window function was removed, it would successfully run, and I'll made an educated guess.
David J.
I am trying to use a window function, but it's not working. The subquery is effectively aggregating.I would advise avoiding using select-list scalar (and particularly correlated) subqueries when the query level in question also contains a group by. Do the group by in a subquery and then in the parent query pull in whatever subselects you want to execute.You need to make it easier for people to help. The first post you sent was just a window function. I asked fordetail and you sent a query without one. Given that discrepancy, added to the fact the query is question is not self-contained, I am less than inclined to try and figure out what you are trying to do.Given me a query that has a window function and looks like, if said window function was removed, it would successfully run, and I'll made an educated guess.
For my own part I need to remember that functional dependencies in the group by would allow one to forgo naming a column there that is present in the select-list...so maybe what you posted would work if run - but it still lacks your attempt at implementing a window function and I don't have the option to play with it since it is not self-contained.
David J.
On May 16, 2016, at 20:48 , David G. Johnston <david.g.johnston@gmail.com> wrote:On Monday, May 16, 2016, Guyren Howe <guyren@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_idSeems 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.statusI 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.