How to put the Order BY in SQL UNION so TOTALS will show up always as last row?

Question

I have SQL UNION where second part of that statement is the row that represents TOTALS. How can I ORDER BY where TOTALS will ALWAYS will show up as the last row?

Answer 1

You could try adding an 'order' column to each query and then order by that column...

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

Answer 2

Add an extra column to the queries being UNIONed, and make that column the first column in your 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'd add a new column like this:

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

That way, regular products appear first, then the total appears at the end.

Answer 3

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

Answer 4

Have you tried using GROUP BY ROLLUP - it might be just want you want, although it's difficult to tell when you haven't posted your query.