Выбрать наиболее часто повторяющиеся строки по группам

Junior Developer

Дата: 22.02.2007 11:30:44

select v.name, v.val
from 
(
 select name, val, count(*) c, max(count(*)) over(partition by name) cm
 from 
 ( 
  select 'a' name, 1 val from dual union all
  select 'a' name, 1 val from dual union all
  select 'a' name, 2 val from dual union all
  select 'b' name, 1 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual
 )
 group by name, val
) v
where v.c = v.cm

1. Как можно сделать проще/лучше/правильней ?
2. Можно ли сразу выбрать только один из возможных максимумов (любой), не оборачивая в ещё один select ?

Хм..

Дата: 22.02.2007 11:35:50

and rownum=1 ?

givanov

Дата: 22.02.2007 11:42:15

И так хорошо.

Junior Developer

Дата: 22.02.2007 11:48:03

Хм..

and rownum=1 ?

Наверное, не совсем понятно указал второй пункт. Нужно получить только одну строку для каждой группы.
Например, в таком случае для группы a в результате будет 2 строки:

select v.name, v.val
from 
(
 select name, val, count(*) c, max(count(*)) over(partition by name) cm
 from 
 ( 
  select 'a' name, 1 val from dual union all
  select 'a' name, 2 val from dual union all
  select 'b' name, 1 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual
 )
 group by name, val
) v
where v.c = v.cm

Хм..

Дата: 22.02.2007 12:25:22

так ?

select v.name, v.val, rn
from 
(
 select name, val, count(*) c, max(count(*)) over(partition by name) cm, row_number() over (partition by name order by count(*) desc) rn
 from 
 ( 
  select 'a' name, 1 val from dual union all
  select 'a' name, 2 val from dual union all
  select 'b' name, 1 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual
 )
 group by name, val
) v
where v.c = v.cm
and rn=1

Junior Developer

Дата: 22.02.2007 13:21:56

Хм..

так ?

select v.name, v.val, rn
from 
(
 select name, val, count(*) c, max(count(*)) over(partition by name) cm, row_number() over (partition by name order by count(*) desc) rn
 from 
 ( 
  select 'a' name, 1 val from dual union all
  select 'a' name, 2 val from dual union all
  select 'b' name, 1 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual
 )
 group by name, val
) v
where v.c = v.cm
and rn=1

Да, примерно это и хотелось увидеть, спасибо.
Уже и свой вариант успел сделать, идея та же:

select * from 
(
 select name, val, count(*) c, max(count(*)) over(partition by name) cm
        , first_value(val) over(partition by name order by count(*) desc) fv 
 from 
 ( 
  select 'a' name, 1 val from dual union all
  select 'a' name, 2 val from dual union all
  select 'b' name, 1 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual
 )
 group by name, val
) v
where v.c = v.cm
      and v.val = v.fv

Volder

Дата: 22.02.2007 17:18:26

по-моему кое-что лишнее...

Хм..

так ?

select v.name, v.val, rn
from 
(
 select name, val, count(*) c, /*max(count(*)) over(partition by name) cm,*/ row_number() over (partition by name order by count(*) desc) rn
 from 
 ( 
  select 'a' name, 1 val from dual union all
  select 'a' name, 2 val from dual union all
  select 'b' name, 1 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual union all
  select 'b' name, 2 val from dual
 )
 group by name, val
) v
where /*v.c = v.cm
and*/ rn=1