Sql-server – Ordering groups/partition windows by other column values

sql serversql server 2014

I have a sample from a database I'm using in SQL server 2014 you can use here http://sqlfiddle.com/#!6/0d98e/1
It looks like this

+--------------+--------------------------+-----------------------------+------------+----+
| Event_number |          Venue           |            Date             | Attendance | rn |
+--------------+--------------------------+-----------------------------+------------+----+
|          247 | Air Canada Centre        | September, 21 2013 00:00:00 |      15504 |  1 |
|          215 | Air Canada Centre        | September, 22 2012 00:00:00 |      16800 |  2 |
|          296 | Allphones Arena          | November, 08 2014 00:00:00  |       9904 |  1 |
|          200 | Allphones Arena          | March, 03 2012 00:00:00     |      14537 |  2 |
|          311 | American Airlines Center | March, 14 2015 00:00:00     |      17160 |  1 |
|          266 | American Airlines Center | March, 15 2014 00:00:00     |      19324 |  2 |
|          344 | Amway Center             | December, 19 2015 00:00:00  |      14459 |  1 |
|          270 | Amway Center             | April, 19 2014 00:00:00     |      17000 |  2 |
|          322 | Arena Ciudad de México   | June, 13 2015 00:00:00      |      21036 |  1 |
|          298 | Arena Ciudad de México   | November, 15 2014 00:00:00  |      21000 |  2 |
+--------------+--------------------------+-----------------------------+------------+----+

The table is about sporting events that have returned to the same venue. I found the two most recent times an event took place at each Venue from another table and partitioned the results by date desc and then I selected the first two rows to produce this sample data.

What I want to do is order this information by Venue pairs (rn 1 and 2), with the Date desc where rn = 1 without splitting up the pairs.

So the result I am looking for would look like this:

+--------------+--------------------------+-----------------------------+------------+----+
| Event_number |          Venue           |            Date             | Attendance | rn |
+--------------+--------------------------+-----------------------------+------------+----+
|          344 | Amway Center             | December, 19 2015 00:00:00  |      14459 |  1 |
|          270 | Amway Center             | April, 19 2014 00:00:00     |      17000 |  2 |
|          322 | Arena Ciudad de México   | June, 13 2015 00:00:00      |      21036 |  1 |
|          298 | Arena Ciudad de México   | November, 15 2014 00:00:00  |      21000 |  2 |
|          311 | American Airlines Center | March, 14 2015 00:00:00     |      17160 |  1 |
|          266 | American Airlines Center | March, 15 2014 00:00:00     |      19324 |  2 |
|          296 | Allphones Arena          | November, 08 2014 00:00:00  |       9904 |  1 |
|          200 | Allphones Arena          | March, 03 2012 00:00:00     |      14537 |  2 |
|          247 | Air Canada Centre        | September, 21 2013 00:00:00 |      15504 |  1 |
|          215 | Air Canada Centre        | September, 22 2012 00:00:00 |      16800 |  2 |
+--------------+--------------------------+-----------------------------+------------+----+

I came up with a pseudo solution which was to basically concatenate each pair of rows into a single row, which I could then order by one of the chosen date columns. But I want to know if it's possible to do it while retaining pairs and for any future queries where I might want to sort partitioned/windowed results by specific guidelines.

Thanks for any help. Please let me know if I didn't provide enough information or if I was unclear in my explanation.

Best Answer

You can use the the max() with an over() clause partitioned by Venue to get the max date for each venue and then use that as the first column in the order by clause.

select T.Event_number,
       T.Venue,
       T.Date,
       T.Attendance,
       T.rn
from (
     select S.Event_number,
            S.Venue,
            S.Date,
            S.Attendance,
            S.rn,
            max(S.Date) over(partition by S.Venue) as MaxDate
     from SampleTable as S
     ) as T
order by T.MaxDate desc,
         T.Venue,
         T.rn;