pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNo sort with except
5+ messages / 4 participants
[nested] [flat]
* No sort with except
@ 2012-03-01 08:13 reto.buchli@wsl.ch
0 siblings, 2 replies; 5+ messages in thread
From: reto.buchli@wsl.ch @ 2012-03-01 08:13 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: No sort with except
@ 2012-03-01 08:16 Frank Lanitz <frank@frank.uvena.de>
parent: reto.buchli@wsl.ch
1 sibling, 1 reply; 5+ messages in thread
From: Frank Lanitz @ 2012-03-01 08:16 UTC (permalink / raw)
To: pgsql-sql
Am 01.03.2012 09:13, schrieb reto.buchli@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,'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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: No sort with except
@ 2012-03-01 08:56 reto.buchli@wsl.ch
parent: Frank Lanitz <frank@frank.uvena.de>
0 siblings, 1 reply; 5+ messages in thread
From: reto.buchli@wsl.ch @ 2012-03-01 08:56 UTC (permalink / raw)
To: pgsql-sql
pgsql-sql-owner@postgresql.org schrieb am 01.03.2012 09:16:53:
> From: Frank Lanitz <frank@frank.uvena.de>
> To: pgsql-sql@postgresql.org,
> Date: 01.03.2012 09:16
> Subject: Re: [SQL] No sort with except
> Sent by: pgsql-sql-owner@postgresql.org
>
> Am 01.03.2012 09:13, schrieb reto.buchli@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,'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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: No sort with except
@ 2012-03-01 09:19 Jasen Betts <jasen@xnet.co.nz>
parent: reto.buchli@wsl.ch
1 sibling, 0 replies; 5+ messages in thread
From: Jasen Betts @ 2012-03-01 09:19 UTC (permalink / raw)
To: pgsql-sql
On 2012-03-01, reto.buchli@wsl.ch <reto.buchli@wsl.ch> wrote:
> Dies ist eine mehrteilige Nachricht im MIME-Format.
> --=_alternative 002D2CF5C12579B4_=
> Content-Type: text/plain; charset="US-ASCII"
>
> 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?
the ORDER BY is evaluated over the final result set, you can bind it to the
except part using parentheses.
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;
)
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: No sort with except
@ 2012-03-01 09:30 Philip Couling <phil@pedal.me.uk>
parent: reto.buchli@wsl.ch
0 siblings, 0 replies; 5+ messages in thread
From: Philip Couling @ 2012-03-01 09:30 UTC (permalink / raw)
To: reto.buchli@wsl.ch; +Cc: pgsql-sql
Hi Reto
You are right to assume that you're query is ordering the second select
and not the whole query. To order the query as a whole it in
parentheses and put the ORDER BY at the end:
(
SELECT foo FROM X
EXCEPT
SELECT foo FROM Y
) ORDER BY foo;
Hope this helps
On 01/03/2012 08:56, reto.buchli@wsl.ch wrote:
>
> pgsql-sql-owner@postgresql.org schrieb am 01.03.2012 09:16:53:
>
>> From: Frank Lanitz <frank@frank.uvena.de>
>> To: pgsql-sql@postgresql.org,
>> Date: 01.03.2012 09:16
>> Subject: Re: [SQL] No sort with except
>> Sent by: pgsql-sql-owner@postgresql.org
>>
>> Am 01.03.2012 09:13, schrieb reto.buchli@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,'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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-03-01 09:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-01 08:13 No sort with except reto.buchli@wsl.ch
2012-03-01 08:16 ` Frank Lanitz <frank@frank.uvena.de>
2012-03-01 08:56 ` reto.buchli@wsl.ch
2012-03-01 09:30 ` Philip Couling <phil@pedal.me.uk>
2012-03-01 09:19 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox