Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 2 down vote accepted

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

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)
    ...
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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