drop table t1Table dropped.
create table t1 (idx number(3), provider varchar2(2), price number(3,2))Table created.
insert into t1 values (1, 'a', 1.01)1 row(s) inserted.
insert into t1 values (2, 'a', 1.02)1 row(s) inserted.
insert into t1 values (3, 'b', 1.02)1 row(s) inserted.
insert into t1 values (4, 'b', 1.03)1 row(s) inserted.
insert into t1 values (5, 'c', 1.04)1 row(s) inserted.
insert into t1 values (6, 'd', 1.05)1 row(s) inserted.
commitStatement processed.
select idx, provider, price, cnt, first_value((case when cnt > 1 then price end)) OVER (order by cnt desc) pricemode
from
(
select idx, provider, price, count(price) over (PARTITION BY price) cnt from t1
)| IDX | PROVIDER | PRICE | CNT | PRICEMODE | 3 | b | 1.02 | 2 | 1.02 | 2 | a | 1.02 | 2 | 1.02 | 6 | d | 1.05 | 1 | 1.02 | 4 | b | 1.03 | 1 | 1.02 | 5 | c | 1.04 | 1 | 1.02 | 1 | a | 1.01 | 1 | 1.02 |
|---|
select idx, provider, price, (select stats_mode(price) xx from t1) pricemode from t1| IDX | PROVIDER | PRICE | PRICEMODE | 1 | a | 1.01 | 1.02 | 2 | a | 1.02 | 1.02 | 3 | b | 1.02 | 1.02 | 4 | b | 1.03 | 1.02 | 5 | c | 1.04 | 1.02 | 6 | d | 1.05 | 1.02 |
|---|