Re: distinct on
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 18 Oct 2012 09:51:28 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
