I agree - it seems an omission, because going back to first principles seems overly complex
SQL>
SQL> create table t ( x int, y int );
Table created.
SQL>
SQL> insert into t values (10,1);
1 row created.
SQL> insert into t values (10,1);
1 row created.
SQL>
SQL> insert into t values (10,2);
1 row created.
SQL> insert into t values (10,2);
1 row created.
SQL> insert into t values (10,2);
1 row created.
SQL> insert into t values (10,2);
1 row created.
SQL>
SQL> insert into t values (10,3);
1 row created.
SQL> insert into t values (10,3);
1 row created.
SQL> insert into t values (10,3);
1 row created.
SQL>
SQL> insert into t values (10,4);
1 row created.
SQL> insert into t values (10,4);
1 row created.
SQL> insert into t values (10,4);
1 row created.
SQL> insert into t values (10,4);
1 row created.
SQL> insert into t values (10,4);
1 row created.
SQL> insert into t values (10,4);
1 row created.
SQL>
SQL> insert into t values (10,5);
1 row created.
SQL> insert into t values (10,5);
1 row created.
SQL> insert into t values (10,5);
1 row created.
SQL>
SQL> insert into t
2 select 20, 6-y from t;
18 rows created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> select x, stats_mode(y)
2 from t
3 group by x;
X STATS_MODE(Y)
---------- -------------
10 4
20 2
SQL>
SQL>
SQL> select x,y, max(case when r=1 then y end) over ( partition by x ) as themode
2 from (
3 select x,y, rank() over ( partition by x order by c desc ) r
4 from
5 (
6 select x, y, count(*) over ( partition by x,y ) c
7 from t
8 )
9 );
X Y THEMODE
---------- ---------- ----------
10 4 4
10 4 4
10 4 4
10 4 4
10 4 4
10 4 4
10 2 4
10 2 4
10 2 4
10 2 4
10 3 4
10 3 4
10 3 4
10 5 4
10 5 4
10 5 4
10 1 4
10 1 4
20 2 2
20 2 2
20 2 2
20 2 2
20 2 2
20 2 2
20 4 2
20 4 2
20 4 2
20 4 2
20 3 2
20 3 2
20 3 2
20 1 2
20 1 2
20 1 2
20 5 2
20 5 2
36 rows selected.
SQL>