title: Selecting SQL TOP N rows from group, row_number, rank not working
body: <p>In SQL Server, I have one table with following fields. I would like to get Top N rows from each group. I have tried following sql query with Rank() and Row_number() but rank and row numbers are not in sequence so not able to limit the rows.</p>
  
  <p>Here is SQL query I tried, as you can see in result, rank column does not have sequential number so "where" clause will not work for top 3 rows for each group.</p>
  
  <p>Similar result observed with ROW_NUMBER(), DENSE_RANK() </p>
  
  <pre><code>  SELECT Rs.RunID,rs.SiteCode,procedure_name,rs.total_cpu_time, rank
  FROM (
      SELECT RunID, SiteCode,total_cpu_time, procedure_name, rank() 
        over (Partition BY sitecode
              ORDER BY total_cpu_time desc ) AS Rank
  
      FROM TopProcs 
   --   ) rs WHERE Rank between 1 and 15 and RunID = 1 and SiteCode in ('CAS', 'P01')
      ) rs WHERE  RunID = 1 and SiteCode in ('CAS', 'P01')
      order by SiteCode`
  </code></pre>
  
  <p>Here is output,</p>
  
  <pre><code>RunID   SiteCode    procedure_name           total_cpu_time rank
  1        CAS        spDRSActivation            117039139161 1
  1        CAS        spDRSSendChangesForGroup    155827022   2
  1        CAS        spDRSMsgBuilderActivation   153595640   3
  1        CAS        spGetChangeNotifications    360607      30
  1        CAS        spDRSSendSyncComplete       100169      65
  1        CAS        spSendRcmServiceBrokerMessage   88270       67
  1        CAS        spLogEntry                  53466       78
  1        P01        spDRSMsgBuilderActivation   62843590384 9
  1        P01        spDRSSendChangesForGroup    62746448352 10
  1        P01        spDrsSummarizeSendHistory   54443397908 13
  1        P01        CH_SummarizePolicyRequests  35371363957 18
  1        P01        spProcessDCMComplianceMsg   29790879064 25
  1        P01        spUpdateComplianceDetails   22106121907 26
  </code></pre>