Обсуждение: INTERSECT AND ORDER BY

Поиск
Список
Период
Сортировка

INTERSECT AND ORDER BY

От
Gary DeSorbo
Дата:
I am trying to use the query below:

SELECT date_worked, hours_worked
FROM hours
WHERE date_worked < '8/15/2001'
INTERSECT
SELECT date_worked, hours_worked
FROM hours
WHERE date_worked > '8/8/2001'
ORDER BY date_worked

but Postgres does not seem to like the ORDER BY     clause. Does
anyone know if it is possible to use ORDER BY in conjunction with an
INTERSECT statement? Does anyone have any alternative suggestions? I
cannot use a temporary table because this is a Web-based application.
Thanks in advance for your help.

Gary

Re: INTERSECT AND ORDER BY

От
Bruno Wolff III
Дата:
You probably want to use:
SELECT date_worked, hours_worked
FROM hours
WHERE date_worked < '8/15/2001'
and date_worked > '8/8/2001'
ORDER BY date_worked

On Wed, Jun 13, 2001 at 11:59:27AM -0700,
  Gary DeSorbo <isasitis@uchicago.edu> wrote:
> I am trying to use the query below:
>
> SELECT date_worked, hours_worked
> FROM hours
> WHERE date_worked < '8/15/2001'
> INTERSECT
> SELECT date_worked, hours_worked
> FROM hours
> WHERE date_worked > '8/8/2001'
> ORDER BY date_worked
>
> but Postgres does not seem to like the ORDER BY     clause. Does
> anyone know if it is possible to use ORDER BY in conjunction with an
> INTERSECT statement? Does anyone have any alternative suggestions? I
> cannot use a temporary table because this is a Web-based application.
> Thanks in advance for your help.
>
> Gary
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: INTERSECT AND ORDER BY

От
Tom Lane
Дата:
Gary DeSorbo <isasitis@uchicago.edu> writes:
> I am trying to use the query below:
> SELECT date_worked, hours_worked
> FROM hours
> WHERE date_worked < '8/15/2001'
> INTERSECT
> SELECT date_worked, hours_worked
> FROM hours
> WHERE date_worked > '8/8/2001'
> ORDER BY date_worked

> but Postgres does not seem to like the ORDER BY     clause.

Ignoring the fact that this is a tremendously inefficient way to do it
(cf. Bruno Wolff's response nearby), it should have worked.  At least
in 7.1, I don't see a problem.  Before 7.1 INTERSECT and EXCEPT had
some limitations ...

            regards, tom lane