Use ORDER BY with UNION ALL

Question

I have a table with an event date column. I want to return records in an order in which first the most recent upcoming records show up and when all the upcoming records are over, the past records should show up with the latest passed first.

For that, I was planning to first get records with event date > GETDATE() sorted in ascending order and merging it with the rest of the records in decreasing order of event date.

As specifying 2 ORDER BY clause is not possible in UNION, what approach could be used in this.

Summarizing, my query wants results like:

SELECT EventName, EventDate
FROM EventTable 
WHERE EventDate>GETDATE()
ORDER BY EventDate

UNION ALL

SELECT EventName, EventDate
FROM EventTable 
WHERE EventDate<=GETDATE()
ORDER BY EventDate DESC

Answer 1

SELECT * 
FROM 
(
  SELECT EventName, EventDate, 1 AS OrderPri, 
    ROW_NUMBER() OVER(ORDER BY EventDate) AS Row
  FROM EventTable 
  WHERE EventDate > GETDATE()
  UNION ALL
  SELECT EventName, EventDate, 2 AS OrderPri, 
    ROW_NUMBER() OVER(ORDER BY EventDate DESC) AS Row
  FROM EventTable 
  WHERE EventDate <= GETDATE()
) AS m
ORDER BY m.OrderPri, m.Row

Answer 2

Another alternative which, like @Linger's answer, uses a CTE, but a bit different because it drops the UNION ALL altogether and performs a single scan instead of two scans (or two seeks, if EventDate has a covering index). This has the same plan as @Damien's second query, but only has to compare to GETDATE() once (I just find that a little tidier).

;WITH x AS 
(
  SELECT EventName, EventDate, s = CASE WHEN EventDate <= GETDATE() THEN 1 END
    FROM dbo.EventTable
)
SELECT EventName, EventDate FROM x
ORDER BY s, CASE s WHEN 1 THEN EventDate END DESC, EventDate;

Please always reference the schema and try to get in the habit of terminating statements with a semi-colon.

Answer 3

You can only add the ORDER BY in the final query:

SELECT EventName, EventDate
FROM EventTable 
WHERE EventDate>GETDATE()

UNION ALL

SELECT EventName, EventDate
FROM EventTable 
WHERE EventDate<=GETDATE()
ORDER BY EventDate DESC

Basically - it's taking the first query, union it with the second and ordering by. If you want a different order by , you'll need to introduce a sorting key in both queries and then sort by that.

However in your example I'd think you'll need to rethink the query, and perhaps look towards ROW_NUMBER or a CASE in the ORDER BY so you don't need to union.

Answer 4

You need to specify an ORDER BY that applies to all of the results, and if you want the results from the first SELECT to appear first, you have to specify that too:

SELECT EventName,EventDate FROM (
  SELECT EventName, EventDate, 1 as ResultSet
  FROM EventTable 
  WHERE EventDate>GETDATE()

  UNION ALL

  SELECT EventName, EventDate, 2
  FROM EventTable 
  WHERE EventDate<=GETDATE()
) t
ORDER BY ResultSet,
         CASE WHEN ResultSet = 1 THEN EventDate END,
         CASE WHEN ResultSet = 2 THEN EventDate END DESC

Strictly, the second CASE expression isn't required, but I've included it for symmetry.

---

Or, as Allan suggests, maybe just:

SELECT EventName, EventDate
FROM EventTable 
ORDER BY
     CASE WHEN EventDate > GETDATE() THEN 1 ELSE 2 END,
     CASE WHEN EventDate > GETDATE() THEN EventDate END,
     EventDate desc

(Where this time I have omitted that final CASE expression)