No sort with except

From: reto(dot)buchli(at)wsl(dot)ch
To: pgsql-sql(at)postgresql(dot)org
Subject: No sort with except
Date: 2012-03-01 08:13:26
Message-ID: OF83C68685.28BB0E71-ONC12579B4.002C0AFA-C12579B4.002D2D18@wsl.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Many Thanks

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Frank Lanitz 2012-03-01 08:16:53 Re: No sort with except
Previous Message Peter Faulks 2012-02-29 23:21:41 Re: date arithmetic with columns