Re: distinct on
Date: Thu, 18 Oct 2012 11:15:04 +0300
Message-ID: <CAN2wOq2ZfwdY=b3XkZQ9AP02H70ssudF07oeze9mwCeDn-gUkA_at_mail.gmail.com>
Thomas speaks about postgres:
http://www.postgresql.org/docs/9.2/interactive/sql-select.html#SQL-DISTINCT SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions
are interpreted using the same rules as for ORDER BY (see above). Note that the"first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. So in PostGres the results are as follows: 1 PG example
select distinct on(a) * from test;
"a";"bb" "aa";"bbb" "ab";"bbb"
2 PG example
select distinct on(a) * from test
order by a, b;
"a";"b" "aa";"bbb" "ab";"bbb"
3 PG example
select distinct on(a) * from test
order by a, b desc;
"a";"bbb" "aa";"bbb" "ab";"bbb"
"Distinct on" without order by is somewhat similar to MySQL group by
without all columns:
1 MySQL example
mysql> select a, b from test group by a;
+------+------+
| a | b |
+------+------+
| a | b | | aa | bbb | | ab | bbb |
+------+------+
3 rows in set (0.00 sec)
But in MySQL one cannot get min or max values for columns not in group by
clause, for example this select gives the same output as previous because
the group by is performed first:
2 MySQL example
mysql> select a, b from test group by a order by a, b desc;
+------+------+
| a | b |
+------+------+
| a | b | | aa | bbb | | ab | bbb |
+------+------+
3 rows in set (0.00 sec)
If one needs to get precise values then subqueries or min() max() could be
used:
3 MySQL example
mysql> select a, max(b) from test group by a;
+------+--------+
| a | max(b) |
+------+--------+
| a | bbb | | aa | bbb | | ab | bbb |
+------+--------+
3 rows in set (0.01 sec)
Oracle has somewhat more restrictions, so as I first worked with Oracle and only afterwards started to work (a bit) with PG and MySQL, these features were quite surprising.
Gints Plivna
http://www.gplivna.eu
2012/10/18 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
>
>
> 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.
>
> Thanks
>
> 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:05 AM
> Subject: Re: distinct on
>
>
> | Jonathan Lewis, 18.10.2012 08:57:
> | >
> | > How does the Postgres specification define the requirements for the
> columns
> | > other than the on() column ?
> | >
> |
> | It uses the ordering defined by the ORDER BY if there is one, otherwise
> it's undefined:
> |
> | From the manual:
> | "Note that the "first row" of each set is unpredictable unless ORDER BY
> is used to ensure that the desired row appears first"
> |
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 18 2012 - 10:15:04 CEST