Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> most frequent and least frequent values query
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 * FROMReceived on Mon Apr 08 2002 - 14:35:46 CDT
(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?
![]() |
![]() |