Re: No sort with except

pgsql-sql-owner(at)postgresql(dot)org schrieb am 01.03.2012 09:16:53:

> From: Frank Lanitz <frank(at)frank(dot)uvena(dot)de>
> To: pgsql-sql(at)postgresql(dot)org,
> Date: 01.03.2012 09:16
> Subject: Re: [SQL] No sort with except
> Sent by: pgsql-sql-owner(at)postgresql(dot)org
>
> Am 01.03.2012 09:13, schrieb reto(dot)buchli(at)wsl(dot)ch:
> > Dear all,
> >
> > When I run the following SQL with PostgreSQL 9.1:
> >
> > --
> > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat,
status
> > FROM person
> >
> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
> > AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
> >
> > ORDER BY pernr, eindt DESC;
> > --
> >
> > it works. I get the most recent persons, even if one came back within
> > this time range.
> >
> > But if i do this:
> >
> > ---
> > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat,
status
> > FROM person
> >
> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
> > AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
> > EXCEPT
> >
> > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat,
> > status
> > FROM person
> > RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid =
10
> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
> > AND ausdt >= TO_CHAR(CURRENT_DATE,'YYYYMMDD')
> > ORDER BY pernr, eindt DESC;
> > ---
> >
> > In this case the ORDER BY does not work: I will get the same person
> > data, either with DESC as with ASC, even when this should change.
> >
> > Does anyone have an explanation for this?
>
>
> Don't you sort just the part at EXCEPT?
>
> Cheers,
> Frank
>
>
Hi Frank
This may be. But as I understand, this will sort the result set. I'm also
not able to place ORDER BY before the EXCEPT.

Am I wrong?

Cheers,
Reto