SQL order by when using a Union

Hello, I am using MS-SQL 2005.
I have this query that pulls invoice totals. It works fine, but.... if I want to include the History table with a Union All I can not get it to sort "Order By" properly.

This works fine....
SELECT
armast.custno AS 'Customer#',
arcust.company AS 'Company',
armast.salesmn AS 'Salesman',
CONVERT (varchar(50), CONVERT (money, SUM(armast.invamt)), 1) AS 'Invoice Total'
FROM armast
LEFT OUTER JOIN arcust ON arcust.custno = armast.custno
WHERE armast.invdte >= '2010-03-01'
 AND armast.invdte <= '2010-03-31'
 AND armast.invamt <> 0 and armast.arstat <> 'V' and armast.invno <> '_RECEIPT'
GROUP BY armast.custno, arcust.company, armast.salesmn
order by sum(armast.invamt) desc

This does not work I get the error
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

SELECT
armast.custno AS 'Customer#',
arcust.company AS 'Company',
armast.salesmn AS 'Salesman',
CONVERT (varchar(50), CONVERT (money, SUM(armast.invamt)), 1) AS 'Invoice Total'
FROM armast
LEFT OUTER JOIN arcust ON arcust.custno = armast.custno
WHERE armast.invdte >= '2010-03-01'
 AND armast.invdte <= '2010-03-31'
 AND armast.invamt <> 0 and armast.arstat <> 'V' and armast.invno <> '_RECEIPT'
GROUP BY armast.custno, arcust.company, armast.salesmn

Union All

SELECT
arymst.custno AS 'Customer#',
arcust.company AS 'Company',
arymst.salesmn AS 'Salesman',
CONVERT (varchar(50), CONVERT (money, SUM(arymst.invamt)), 1) AS 'Invoice Total'
FROM arymst
LEFT OUTER JOIN arcust ON arcust.custno = arymst.custno
WHERE arymst.invdte >= '2010-03-01'
 AND arymst.invdte <= '2010-03-31'
 AND arymst.invamt <> 0 and arymst.arstat <> 'V' and arymst.invno <> '_RECEIPT'
GROUP BY arymst.custno, arcust.company, arymst.salesmn
order by sum(invamt) desc

How can I get this to order the amts correctly?

Thanks
LVL 1
Wildone63Asked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
select * from (
SELECT
armast.custno AS 'Customer#',
arcust.company AS 'Company',
armast.salesmn AS 'Salesman',
CONVERT (varchar(50), CONVERT (money, SUM(armast.invamt)), 1) AS 'Invoice Total',
sum(invamt) as SortAmount
FROM armast
LEFT OUTER JOIN arcust ON arcust.custno = armast.custno
WHERE armast.invdte >= '2010-03-01'
 AND armast.invdte <= '2010-03-31'
 AND armast.invamt <> 0 and armast.arstat <> 'V' and armast.invno <> '_RECEIPT'
GROUP BY armast.custno, arcust.company, armast.salesmn

Union All

SELECT
arymst.custno AS 'Customer#',
arcust.company AS 'Company',
arymst.salesmn AS 'Salesman',
CONVERT (varchar(50), CONVERT (money, SUM(arymst.invamt)), 1) AS 'Invoice Total',
sum(invamt) as SortAmount
FROM arymst
LEFT OUTER JOIN arcust ON arcust.custno = arymst.custno
WHERE arymst.invdte >= '2010-03-01'
 AND arymst.invdte <= '2010-03-31'
 AND arymst.invamt <> 0 and arymst.arstat <> 'V' and arymst.invno <> '_RECEIPT'
GROUP BY arymst.custno, arcust.company, arymst.salesmn
) a
order by sortamount asc
0
 
Wildone63Author Commented:
Brilliant!!!
Thank You very much!
0
 
Brendt HessSenior DBACommented:
The simplest way to order a UNION is with the ordinal value of the column, e.g.:

ORDER BY 4

However, as you are outputting a formatted (varchar) version, this simple version will not work.  So, you need to add a level of indirection to your code, such as by creating a derived table, as in the example below:


SELECT 
	custno AS 'Customer#',
	company AS 'Company',
	salesmn AS 'Salesman',
	CONVERT (varchar(50), CONVERT (money, invamt), 1) AS 'Invoice Total'
FROM (
	SELECT
		armast.custno,
		arcust.company,
		armast.salesmn,
		SUM(armast.invamt) AS invamt
	FROM armast
	LEFT OUTER JOIN arcust 
		ON arcust.custno = armast.custno
	WHERE armast.invdte >= '2010-03-01'
		AND armast.invdte <= '2010-03-31'
		AND armast.invamt <> 0 and armast.arstat <> 'V' and armast.invno <> '_RECEIPT'
	GROUP BY armast.custno, arcust.company, armast.salesmn

	Union All

	SELECT
		arymst.custno,
		arcust.company,
		arymst.salesmn,
		SUM(arymst.invamt)
	FROM arymst
	LEFT OUTER JOIN arcust 
		ON arcust.custno = arymst.custno
	WHERE arymst.invdte >= '2010-03-01'
		AND arymst.invdte <= '2010-03-31'
		AND arymst.invamt <> 0 and arymst.arstat <> 'V' and arymst.invno <> '_RECEIPT'
	GROUP BY arymst.custno, arcust.company, arymst.salesmn
	) Src
ORDER BY invamt
--- alternate: Order By 4

Open in new window

0
All Courses

From novice to tech pro — start learning today.