Re: most frequent and least frequent values query

mikharakiri_at_yahoo.com (Mikito Harakiri) wrote in message news:<bdf69bdf.0204081135.1f0c0b4_at_posting.google.com>...
> I'm trying to write a query, that returns the most frequent and the
> least frequent value. For example,
>
> val
> ---
> 2
> 1
> 1
> 2
> 2
> 4
> 3
> 4
>
> the most frequent value is 2, while the least frequent value is 3.
> Since
>
> select val, count(1) cnt from tbl group by val
>
> already gives me the value distibution from where I need just to cut
> off "min" and "max" rows, I assumed that it's fairly easy to do.
>
> Yet, my final solutions are extremely ugly. I can use corelated
> subqueries like this:
>
> select val from (select val, count(1) cnt from tbl group by val)
> where cnt = (select max(cnt) from
> (select val, count(1) cnt from tbl group by val)
> )
>
> and refactor the common part into "with" clause, or use analytic query
> like this:
>
> SELECT * FROM
> (SELECT val maxval,
> count(1),
> RANK() OVER (ORDER BY count(1) DESC ) AS COUNT_RANK
> FROM tbl
> GROUP BY val),
> (SELECT val minval,
> count(1),
> RANK() OVER (ORDER BY count(1) ASC ) AS COUNT_RANK
> FROM tbl
> GROUP BY val)
> WHERE rownum < 2;
>
> This query contains cartesian product, which I can suppress by either
> joining by rank or, better yet, by pushing rownum predicate into both
> operands.
>
> Anyway, the none of my final solution is either pretty or performant.
> Is there an analytic query that I'm missing?

A solution

WITH
   distr as (select val, count(1) cnt from tbl group by val) select * from
  (select val maxval from distr
  where cnt = (select max(cnt) from distr)   and rownum < 2),
  (select val minval from distr
  where cnt = (select min(cnt) from distr)   and rownum < 2)

has essentially the same runtime performance as just

select val, count(1) cnt from tbl group by val

Therefore I'm basically OK with it. My question still remains if there is a simpler solution with windowing function. Received on Mon Apr 08 2002 - 19:51:34 CDT