return top N rows and have remaining rows rolled up into a single row - oracle sql

Question

I am trying to get a result set with the top N rows from the query, and have the remaining rows rolled up into a single row. I have come up with a query as below - i am needing suggestions about using any built-in oracle sql functions that can help with this scenario and eliminate a lot of the redundancy i have in this sql.

    select label, count_id from 
(
select table1.NAME as label, count(table1.id) as count_id, 
      ROW_NUMBER() OVER (order by  count(table1.id) desc) AS rn  
      from table1 
      where table1.NAME like 'D%'
      group by table1.NAME
      )
      where rn <= 9  -- get top 9 rows
union

select 'Other' as label, sum(count_id) as count_id from 
(
select label, count_id from 
(
select table1.NAME as label, count(table1.id) as count_id, 
      ROW_NUMBER() OVER (order by  count(table1.id) desc) AS rn  
      from table1 
      where table1.NAME like 'D%'
      group by table1.NAME
      )
      where rn > 9 -- get rows after row-num 9
 ) 

please share if you have any suggestions on improving this query.

Answer 1

This appears to be a textbook case for illustrating subquery refactoring.

Here are my suggestions:

WITH q AS
(
SELECT table1.NAME AS label, COUNT(table1.id) AS count_id, 
      ROW_NUMBER() OVER (ORDER BY COUNT(table1.id) DESC) AS rn  
  FROM table1 
 WHERE table1.name LIKE 'D%'
 GROUP BY table1.name
)
SELECT label, count_id FROM q WHERE rn <= 9
UNION ALL
SELECT 'Other' AS label, SUM(count_id) AS count_id 
  FROM q
 WHERE rn > 9 
 GROUP BY 'Other';

On the table in my database I tried this on I actually got a cost improvement as well - YMMV.