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
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 |