3

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
2

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 <= 50 --top X rows per timezone
ORDER BY timezone, RowNumber
2

You're sorting by the row number, partitioned by timezone. This is resulting in the first instance of each timezone being number 1, the second instance of each timezone being number 2, etc.

Therefore the results are round-robin ordered.

Look at this query to see that:

SELECT ID
    , event_date
    , timezone
    , rn = ROW_NUMBER() OVER(PARTITION BY timezone ORDER BY timezone, event_date)
FROM dbo.TZ
ORDER BY rn;
╔════╦═════════════════════════╦══════════╦════╗
║ ID ║ event_date              ║ timezone ║ rn ║
╠════╬═════════════════════════╬══════════╬════╣
║ 10 ║ 1905-06-24 00:00:00.000 ║ Central  ║ 1  ║
║ 11 ║ 1905-06-24 00:00:00.000 ║ Eastern  ║ 1  ║
║ 9  ║ 1905-06-24 00:00:00.000 ║ Mountain ║ 1  ║
║ 12 ║ 1905-06-24 00:00:00.000 ║ Western  ║ 1  ║
║ 8  ║ 1905-06-25 00:00:00.000 ║ Western  ║ 2  ║
║ 5  ║ 1905-06-25 00:00:00.000 ║ Mountain ║ 2  ║
║ 7  ║ 1905-06-25 00:00:00.000 ║ Eastern  ║ 2  ║
║ 6  ║ 1905-06-25 00:00:00.000 ║ Central  ║ 2  ║
║ 2  ║ 1905-06-26 00:00:00.000 ║ Central  ║ 3  ║
║ 3  ║ 1905-06-26 00:00:00.000 ║ Eastern  ║ 3  ║
║ 1  ║ 1905-06-26 00:00:00.000 ║ Mountain ║ 3  ║
║ 4  ║ 1905-06-26 00:00:00.000 ║ Western  ║ 3  ║
╚════╩═════════════════════════╩══════════╩════╝

The ordering you want can be accomplished without the aggregate:

SELECT ID, event_date, timezone
FROM dbo.TZ
ORDER BY timezone, event_date;
╔════╦═════════════════════════╦══════════╗
║ ID ║ event_date              ║ timezone ║
╠════╬═════════════════════════╬══════════╣
║ 10 ║ 1905-06-24 00:00:00.000 ║ Central  ║
║ 6  ║ 1905-06-25 00:00:00.000 ║ Central  ║
║ 2  ║ 1905-06-26 00:00:00.000 ║ Central  ║
║ 11 ║ 1905-06-24 00:00:00.000 ║ Eastern  ║
║ 7  ║ 1905-06-25 00:00:00.000 ║ Eastern  ║
║ 3  ║ 1905-06-26 00:00:00.000 ║ Eastern  ║
║ 9  ║ 1905-06-24 00:00:00.000 ║ Mountain ║
║ 5  ║ 1905-06-25 00:00:00.000 ║ Mountain ║
║ 1  ║ 1905-06-26 00:00:00.000 ║ Mountain ║
║ 12 ║ 1905-06-24 00:00:00.000 ║ Western  ║
║ 8  ║ 1905-06-25 00:00:00.000 ║ Western  ║
║ 4  ║ 1905-06-26 00:00:00.000 ║ Western  ║
╚════╩═════════════════════════╩══════════╝

You can use a cte to obtain the desired row numbering to limit each timezone to the top 50 entries like this:

;WITH cte AS 
(
    SELECT ID
        , event_date
        , timezone
        , rn = ROW_NUMBER() OVER(PARTITION BY timezone ORDER BY timezone, event_date)
    FROM dbo.TZ
)
SELECT *
FROM cte
WHERE cte.rn <= 50
ORDER BY cte.timezone
    , event_date;
╔════╦═════════════════════════╦══════════╦════╗
║ ID ║ event_date              ║ timezone ║ rn ║
╠════╬═════════════════════════╬══════════╬════╣
║ 10 ║ 1905-06-24 00:00:00.000 ║ Central  ║ 1  ║
║ 6  ║ 1905-06-25 00:00:00.000 ║ Central  ║ 2  ║
║ 2  ║ 1905-06-26 00:00:00.000 ║ Central  ║ 3  ║
║ 11 ║ 1905-06-24 00:00:00.000 ║ Eastern  ║ 1  ║
║ 7  ║ 1905-06-25 00:00:00.000 ║ Eastern  ║ 2  ║
║ 3  ║ 1905-06-26 00:00:00.000 ║ Eastern  ║ 3  ║
║ 9  ║ 1905-06-24 00:00:00.000 ║ Mountain ║ 1  ║
║ 5  ║ 1905-06-25 00:00:00.000 ║ Mountain ║ 2  ║
║ 1  ║ 1905-06-26 00:00:00.000 ║ Mountain ║ 3  ║
║ 12 ║ 1905-06-24 00:00:00.000 ║ Western  ║ 1  ║
║ 8  ║ 1905-06-25 00:00:00.000 ║ Western  ║ 2  ║
║ 4  ║ 1905-06-26 00:00:00.000 ║ Western  ║ 3  ║
╚════╩═════════════════════════╩══════════╩════╝

Depending on the number of rows in your source table, it may be advantageous to have an index on timezone and event_date, and including the ID column (assuming that is not the primary key for the table).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.