Re: distinct on

Thomas,

Thanks for the examples.
It's always interesting to see how many different strategies (or versions of syntax) there are to solve the same problem.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Thomas Kellerer" <thomas.kellerer_at_mgm-tp.com> To: <oracle-l_at_freelists.org> Sent: Thursday, October 18, 2012 8:51 AM Subject: Re: distinct on
  |
  | 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.
  |
  

--
http://www.freelists.org/webpage/oracle-l