Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> most frequent and least frequent values query

most frequent and least frequent values query

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 8 Apr 2002 12:35:46 -0700
Message-ID: <bdf69bdf.0204081135.1f0c0b4@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?
Received on Mon Apr 08 2002 - 14:35:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US