| 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 * 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
![]() |
![]() |