CODEWITHSUNDEEP
oracle-databasetop-n
Santosh
Santosh

Reputation: 2495

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

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.

Upvotes: 0

Views: 596

Answers (3)

trincot
trincot

Reputation: 350167

If you are on Oracle 12, you can do:

select   value, count(*)
from     mytable
group by value
order by 2
fetch first 1 rows only

If you need to have all values that share the minimum count, then replace only with with ties.

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112324

You can use a GROUP BY, order by the count and finally select the first row

SELECT * FROM (
  SELECT value, count(*) as cnt
  FROM sometable
  GROUP BY value
  ORDER BY count(*)
) WHERE ROWNUM = 1

sqlfiddle: http://sqlfiddle.com/#!4/e5f075/1

Upvotes: 1

user5683823
user5683823

Reputation:

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

Upvotes: 1

Related Questions