title: return top N rows and have remaining rows rolled up into a single row - oracle sql
body: <p>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. </p>
  
  <pre><code>    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 &lt;= 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 &gt; 9 -- get rows after row-num 9
   ) 
  </code></pre>
  
  <p>please share if you have any suggestions on improving this query.</p>