Oracle restrict rows based on category

I have a DB structure like below:

id  value  category
1   val1     6
2   val2     6
3   val3     5
4   val4     7
5   val5     8
5   val5     5

I need to display the result based on the number of category after sorting. Suppose if value of category is 2 then results should be:

id  value  category
    3   val3     5
    5   val5     5
    1   val1     6
    2   val2     6

This is a dynamic table and after some time records with category 6 may get deleted and now if category is 2, we need to display:

id  value  category
3   val3     5
5   val5     5
4   val4     7

After research one solution I found is using rownum:

SELECT * FROM TABLE WHERE CATEGORY IN
(SELECT CATEGORY FROM 
(SELECT ROWNUM ROWNUMBER, CATEGORY FROM 
(SELECT CATEGORY FROM TABLE GROUP BY CATEGORY ORDER BY CATEGORY)) WHERE ROWNUMBER >= 2)

But I know this is not very efficient. Is there any more optimized way of doing it ?

Solution 1:

You seem to want to select n categories with the highest counts. If so:

select t.*
from (select t.*,
             dense_rank() over (order by cnt desc, category desc) as seqnum
      from (select t.*, count(*) over (partition by category) cnt
            from t
           ) t
     ) t
where seqnum <= 2;