2

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.

2

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;
  • 1
    You don't even need a subquery: SQLfiddle (it's Julien's query, your and yours without subquery) – ypercubeᵀᴹ Feb 9 '16 at 8:01
1

You can use CROSS APPLY to get the MAX(date) for each Venue and then ORDER BY it:

SELECT Event_number, Venue, [Date], Attendance, rn
FROM dbo.SampleTable s
CROSS APPLY (
    SELECT md = MAX([Date]) 
    FROM dbo.SampleTable 
    WHERE s.Venue = Venue
) ca
ORDER BY ca.md DESC, Venue, s.rn

SQL Fiddle

By the way, you can insert into the sample table with 1 single insert:

INSERT [dbo].[SampleTable] ([Event_number], [Venue], [Date], [Attendance], [rn]) 
VALUES 
    (247, N'Air Canada Centre', CAST(N'2013-09-21 00:00:00.000' AS DateTime), 15504, 1)
    , (215, N'Air Canada Centre', CAST(N'2012-09-22 00:00:00.000' AS DateTime), 16800, 2)
    ...

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.