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.
  

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-l
Received on Thu Oct 18 2012 - 09:51:28 CEST

Original text of this message