Menu
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?
733
As per standard SQL UNION / UNION ALL do not guarantee any particular sort order without an outer ORDER BY clause - like there is hardly any place in SQL where sort order is guaranteed without ORDER BY .
As the following query shows, when you include an ORDER BY clause, it must follow the final SELECT statement and use an integer, not an identifier, to refer to the ordering column. Ordering takes place after the set operation is complete.
The UNION ALL operator can use the ORDER BY clause to order the results of the query in SQL Server (Transact-SQL).
Here is the query to count on the union query. mysql> select count(*) as UnionCount from -> ( -> select distinct UserId from union_Table1 -> union -> select distinct UserId from union_Table2 -> )tbl1; The following is the output displaying the count.
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.
51
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.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
