How to put a BY order in SQL UNION so that TOTALS are always displayed as the last line?

Question

I have a SQL UNION where the second part of this statement is a string that represents TOTALS. How can I ORDER, where TOTAL will ALWAYS be displayed as the last line?

Answer 1

Add an extra column to UNIONed queries and make this column the first column in the ORDER BY clause.

So, if I started with something like this:

SELECT product, price
FROM table
UNION 
SELECT 'Total' AS product, SUM(price)
FROM table

I would add a new column:

SELECT product, price
FROM (
  SELECT product, price, 0 AS union_order
  FROM table
  UNION
  SELECT 'Total' AS product, SUM(price), 1 AS union_order
  FROM table
)
ORDER BY union_order

Thus, ordinary products first appear, then the total number appears at the end.

Answer 2

GROUP BY ROLLUP - , , , .

Answer 3

You can try adding an order column to each query, and then sort by that column ...

SELECT x.*
FROM
(
SELECT columns, 2 AS [Order]
UNION 
SELECT totals, 1 AS [Order]
) x
ORDER BY x.[Order]

Answer 4

select * from
(
  select 1 as prio
  , col1
  , col2
  ...
  from tableA
     union
  select 2 as prio
  , totalCol1
  , totalCol2
  ...
  from tableB
) order by prio