Re: most frequent and least frequent values query

From: Jaap \(myweb\) <j.w.vandijk.remove_this_at_hetnet.nl>
Date: Wed, 10 Apr 2002 10:19:19 +0200
Message-ID: <a90sfo$aau$1@cyan.nl.gxn.net>

You can use an ORDER BY in your subquery:

select val
from (select val,count(*) cnt from tbl group by val order by cnt) where rownum < 2

Jaap.

"Mikito Harakiri" <mikharakiri_at_yahoo.com> schreef in bericht news:bdf69bdf.0204081651.633bdb6e_at_posting.google.com...
> 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 Wed Apr 10 2002 - 03:19:19 CDT