Reorder partitioned results by specific column I have a query where I want to find the top 50 event_dates for each time zone. While I am getting that, it's not currently formatted in a usable manner. I want the results to look something like:


  ID  |  event_date  |  timezone
  
  1   | 2017-08-07  | Mountain
  
  2   | 2017-08-08 |    Mountain
  
  3   | 2017-08-09 |    Mountain
  
  4   | 2017-08-07 |    Central
  
  5   | 2017-08-08  | Central
  
  6   | 2017-08-09 |    Central
  
  7   | 2017-08-07 |    Eastern
  
  8   | 2017-08-08 |    Eastern
  
  9   | 2017-08-09 |       Eastern
  
  10  | 2017-08-07 |    Western
  
  11  | 2017-08-08 |    Western
  
  12  | 2017-08-09 |    Western


Currently, the results are being mixed between the timezones like this:


  ID  |  event_date  |  timezone
  
  1   | 2017-08-07 | Mountain
  
  2   | 2017-08-07 |    Central
  
  3   | 2017-08-07 |    Eastern
  
  4   | 2017-08-07 |    Western
  
  5   | 2017-08-08 | Mountain
  
  6   | 2017-08-08 |    Central
  
  7   | 2017-08-08 |    Eastern
  
  8   | 2017-08-08 |    Western
  
  9   | 2017-08-09 |       Mountain
  
  10  | 2017-08-09 |    Central
  
  11  | 2017-08-09 |    Eastern
  
  12  | 2017-08-09 |    Western


What would I need to do to update the query to output in that manner?

For reference, what I currently have is something like this:

SELECT TOP 200 ID, event_date, timezone FROM ...
ORDER BY ROW_NUMBER() OVER(PARTITION BY timezone ORDER BY timezone), timezone, event_date ASC
I would get the ROW_NUMBER logic happening first, by enclosing it in a sub-query, and then filtering by the top 50 rows per timezone. This also makes the query immune to new timezones being added to your FROM table.

SELECT *
FROM (
    SELECT ID
        ,event_date
        ,timezone
        ,ROW_NUMBER() OVER (
                PARTITION BY timezone --these are the main groups you want
                ORDER BY event_date DESC --DESC picks the LATEST dates first (ASC would pick the earliest)
            ) AS RowNumber
    FROM...
    ) Results
WHERE RowNumber &lt;= 50 --top X rows per timezone
ORDER BY timezone, RowNumber