Re: distinct on
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <thomas.kellerer@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Oct 2012 08:59:14 +0100
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@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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.
|
--
//www.freelists.org/webpage/oracle-l
Other related posts:
