Set The Order Query Results Display

Question

I need to display my emailType in DESC order. Now it is a varchar field so I need it to be reverse alphabet order Z - A. When I run my query it is displayed in ASC order, and since I am using a UNION I can't add an ORDER BY clause.

What change should I make to this query so I am able to set the the order the emailType displays in?

SELECT 
emailType
,aprilTotals
,mayTotals
,juneTotals
,SUM(aprilTotals+mayTotals+juneTotals) As Q2
,julyTotals
,augustTotals
,septemberTotals
,SUM(julyTotals+augustTotals+septemberTotals) As Q3
,octoberTotals
,novemberTotals
,decemberTotals
,SUM(octoberTotals+novemberTotals+decemberTotals) As Q4
,SUM(aprilTotals+mayTotals+juneTotals+julyTotals+augustTotals+septemberTotals+octoberTotals+novemberTotals+decemberTotals) As YearlyTotals
FROM TestDB.custData 
GROUP by emailType
UNION ALL
Select
'Total Emails' As Total
,SUM(aprilTotals) As April
,SUM(mayTotals) As May
,SUM(juneTotals) As June
,SUM(aprilTotals+mayTotals+juneTotals) As Q2
,SUM(julyTotals) As July
,SUM(augustTotals) As August
,SUM(septemberTotals) As September
,SUM(julyTotals+augustTotals+septemberTotals) As Q3
,SUM(octoberTotals) As October
,SUM(novemberTotals) As November
,SUM(decemberTotals) As December
,SUM(octoberTotals+novemberTotals+decemberTotals) As Q4
,SUM(aprilTotals+mayTotals+juneTotals+julyTotals+augustTotals+septemberTotals+octoberTotals+novemberTotals+decemberTotals) As YearTotals
FROM TestDB.custData

Answer 1

(SELECT ... )
UNION
(SELECT ... )
ORDER BY ...

Works just fine. It first does the two SELECTs, collecting both sets of results into a temp table, dedups (if UNION DISTINCT), and finally does the outer ORDER BY.

You can optionally have ORDER BY and LIMIT inside each SELECT.

Use parentheses (as I did) to avoid having the ORDER BY glom onto the second SELECT.

Answer 2

You can put () around the select union select and order the hole result

Like

(SELECT 
emailType
,aprilTotals
,mayTotals
,juneTotals
,SUM(aprilTotals+mayTotals+juneTotals) As Q2
,julyTotals
,augustTotals
,septemberTotals
,SUM(julyTotals+augustTotals+septemberTotals) As Q3
,octoberTotals
,novemberTotals
,decemberTotals
,SUM(octoberTotals+novemberTotals+decemberTotals) As Q4
,SUM(aprilTotals+mayTotals+juneTotals+julyTotals+augustTotals+septemberTotals+octoberTotals+novemberTotals+decemberTotals) As YearlyTotals
FROM TestDB.custData 
GROUP by emailType
UNION ALL
Select
'Total Emails' As Total
,SUM(aprilTotals) As April
,SUM(mayTotals) As May
,SUM(juneTotals) As June
,SUM(aprilTotals+mayTotals+juneTotals) As Q2
,SUM(julyTotals) As July
,SUM(augustTotals) As August
,SUM(septemberTotals) As September
,SUM(julyTotals+augustTotals+septemberTotals) As Q3
,SUM(octoberTotals) As October
,SUM(novemberTotals) As November
,SUM(decemberTotals) As December
,SUM(octoberTotals+novemberTotals+decemberTotals) As Q4
,SUM(aprilTotals+mayTotals+juneTotals+julyTotals+augustTotals+septemberTotals+octoberTotals+novemberTotals+decemberTotals) As YearTotals
FROM TestDB.custData)
ORDER BY emailType  DESC;

Answer 3

I believe you are required to review the way you save your data. From the example you posted the table is poorly structured and in any case it is not correct to make the UNION with two different data sets.

inside the SELECT you can use this to obtain a row number which you can subsequently use for your data sorts

ROW_NUMBER() over (order by emailType desc) as rownum