How to get minimum number of records for a condition in oracle

Question

I want to get a minimum number of record based on my condition I give in where condition.

for ex: I have a table with two columns(Id, Value)

My table has data like following:

Id   Value
 1    001
 2    001
 3    001
 4    002 
 5    002
 6    003 
 7    004
 8    004 
 9    004
 10   004

From the above table Value '001' has 3 Ids(1,2,3) and Value '002' has 2 and so on.

Now I want to identify the Value which has minimum Ids(like in this example, it should be Value '003' with only one Id).

How to write a query for this in Oracle.?

Thanks in advance.

Answer 1

The query below will select the value (or values) with the lowest row count. In the case of ties, all the values with the same, smallest number of rows are selected. The row count is not shown, but it could be shown easily (add it to the outer select).

The real work is done in the aggregate subquery. In addition to the value and the count of rows, we also compute the analytic function min(count(*)) - over the entire result of the aggregation, so the analytic clause is in fact empty: over ().

select value
from   (
         select   value, count(*) as cnt, min(count(*)) over () as min_cnt
         from     your_table
         group by value
       )
where  cnt = min_cnt