Re: distinct on
From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Thu, 18 Oct 2012 09:51:28 +0200
Message-ID: <507FB500.5060302_at_mgm-tp.com>
Jonathan Lewis, 18.10.2012 09:36:
> Thomas,
>
> Just to avoid any confusion - is your response about the Postgres on()
> clause example that we saw, or are you talking about the analytic function
> implementation in Oracle. The latter is something I know, the former is
> what I was curious about - especially since (possibly coincidentally) the
> selected "b column" value in the example happened to be the middle (both in
> value and position) of the original list. If your reply was related to
> Postgres - can you give us a version of the Postgres example that shows the
> "order by" in place.
(7 rows)
(3 rows)
a | b
----+------
(3 rows)
a | b
----+------
(3 rows)
Date: Thu, 18 Oct 2012 09:51:28 +0200
Message-ID: <507FB500.5060302_at_mgm-tp.com>
Jonathan Lewis, 18.10.2012 09:36:
> Thomas,
>
> Just to avoid any confusion - is your response about the Postgres on()
> clause example that we saw, or are you talking about the analytic function
> implementation in Oracle. The latter is something I know, the former is
> what I was curious about - especially since (possibly coincidentally) the
> selected "b column" value in the example happened to be the middle (both in
> value and position) of the original list. If your reply was related to
> Postgres - can you give us a version of the Postgres example that shows the
> "order by" in place.
Sorry, you are right. I should have mentioned that.
The order by is the one specified for the whole query.
I have extended the initial example with an additional "sort column" to be able to show the effect:
postgres=> select * from test;
a | b | some_order
----+------+------------
a | b | 1 a | bb | 3 a | bbb | 2 ab | bbb | 1 aa | bbb1 | 3 aa | bbb2 | 1 aa | bbb3 | 2
(7 rows)
postgres=> select distinct on (a) a,b
postgres-> from test;
a | b
----+------
a | bb aa | bbb3 ab | bbb
(3 rows)
postgres=> select distinct on (a) a,b postgres-> from test postgres-> order by a, some_order;
a | b
----+------
a | b aa | bbb2 ab | bbb
(3 rows)
postgres=> select distinct on (a) a,b postgres-> from test postgres-> order by a, some_order desc;
a | b
----+------
a | bb aa | bbb1 ab | bbb
(3 rows)
Postgres requires you to put the column(s) mentioned in the "distinct on" clause to be the first ones in the order by clause.
So this:
select distinct on (a) a,b
from test
order by some_order
will be rejected. The result must be sorted by the "distinct" column(s) first.
Regards
Thomas
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 18 2012 - 09:51:28 CEST