From: | Guyren Howe <guyren(at)gmail(dot)com> |
---|---|
To: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | FIRST_VALUE: need to group by argument? |
Date: | 2016-05-24 03:48:26 |
Message-ID: | 9E2A33B9-D18F-4FDF-BBF9-97F14B75C329@gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am missing something here.
I have two tables:
orders
id
delivery_route_segments
id,
order_id,
position,
completed
I want to find the first uncompleted deliver_route_segment for each order, by position. Seems to me I ought to be able to do this:
SELECT
o.id,
FIRST_VALUE(drs.id)
FROM
orders o JOIN
delivery_route_segments drs ON (drs.order_id = o.id AND NOT drs.completed)
GROUP BY
o.id
but I'm told I need an over clause.
So I try this:
SELECT
o.id,
FIRST_VALUE(drs.id) OVER (ORDER BY position ASC)
FROM
orders o JOIN
delivery_route_segments drs ON (drs.order_id = o.id AND NOT drs.completed)
GROUP BY
o.id
here I'm told "drs.id must appear in the GROUP BY clause". This doesn't make sense to me; I shouldn't need to group by a value that's inside an aggregate function.
Tried this.
SELECT
o.id,
FIRST_VALUE(drs.id) OVER (PARTITION BY o.id ORDER BY position ASC)
FROM
orders o JOIN
delivery_route_segments drs ON (drs.order_id = o.id AND NOT drs.completed)
GROUP BY
o.id
but it has the same problem.
I can solve this with a subquery, but:
- I'd still like to know what's wrong; and
- I expect the subquery to be slower (yes?)
From | Date | Subject | |
---|---|---|---|
Next Message | Rader, David | 2016-05-24 04:00:58 | Re: Postgresql-fdw |
Previous Message | Melvin Davidson | 2016-05-24 00:57:41 | Re: Unused indexes - PostgreSQL 9.2 |