At the end of my last MySQL post I mentioned strange behavior with GROUP BY and DISTINCT. In Oracle such constructions are not valid and produce an error.

Here is the test table and statements:

create table t1 (c1 int, c2 int);

insert into t1 values (1,10);
insert into t1 values (2,20);
insert into t1 values (3,30);
insert into t1 values (4,11);
insert into t1 values (4,40);
insert into t1 values (5,50);
insert into t1 values (5,12);

select c1,count(*) cnt from t1 group by c1 order by c2;

select distinct c1 from t1 order by c2;

(more…)