[SQL] No sort with except

[reto . buchli]

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,'MMDD')
AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 

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,'MMDD')
AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 
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,'MMDD')
AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 
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?

Many Thanks

Re: [SQL] No sort with except

[reto . buchli]

pgsql-sql-ow...@postgresql.org schrieb am 01.03.2012 09:16:53:

> From: Frank Lanitz 
> To: pgsql-sql@postgresql.org, 
> Date: 01.03.2012 09:16
> Subject: Re: [SQL] No sort with except
> Sent by: pgsql-sql-ow...@postgresql.org
> 
> Am 01.03.2012 09:13, schrieb reto.buc...@wsl.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,'MMDD')
> > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
> > 
> > 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,'MMDD')
> > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
> > 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,'MMDD')
> > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
> > 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