FIRST_VALUE: need to group by argument?

Поиск
Список
Период
Сортировка
От Guyren Howe
Тема FIRST_VALUE: need to group by argument?
Дата
Msg-id 9E2A33B9-D18F-4FDF-BBF9-97F14B75C329@gmail.com
обсуждение исходный текст
Ответы Re: FIRST_VALUE: need to group by argument?
Список 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?)

В списке pgsql-general по дате отправления: