I need to print the Region ID and the count of countries he holds, but only for the highest one.

My table is like this:

COUNTRY_ID        COUNTRY_NAME     REGION_ID
AR                Argentina           2
AU                Belgium             3
BE                Brazil              1
BR                Canada              2
CA                Switzerland         2
CN                China               1
DE                Germany             3

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

And the result must be something like:

REGION_ID      Quantity
   2              3

<iframe name="sif2" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

CodePudding user response：

You can use below sql for that purpose. I use row_number analytic function to rank the rows after being grouped by REGION_ID column. Then, I filter by rnb = 1 to get the row with maximun quantity.

select REGION_ID, QUANTITY
from (
    select t.REGION_ID, count(*)quantity, row_number()over(order by count(*) desc) rnb
    from YourTable t
    group by t.REGION_ID
)
where rnb = 1

demo

CodePudding user response：

From Oracle 12c, you can use:

SELECT region_id,
       COUNT(*) AS quantity
FROM   table_name
GROUP BY region_id
ORDER BY quantity DESC
FETCH FIRST ROW ONLY;

Before that:

SELECT *
FROM   (
  SELECT REGION_ID,
         COUNT(*) AS quantity
  FROM   table_name
  GROUP BY region_id
  ORDER BY quantity DESC
)
WHERE  ROWNUM = 1;