[SQL Server] Use ORDER BY in query UNION

Databases
1

Hello there.
First of all I must say that I am a newbie when it comes to SQL Server 2008.

«UNION does nothing other than stack the data of multiple result sets in one result sets.
While using the UNION you have to consider few things like: column name, number and aliases should be mentioned in first part of the SELECT.»

Here is my problem:
ORDER BY clause will sort the complete result sets, it will use the column name of first SELECT statement and should be write down after the last SELECT statement.

I can not use ORDER BY in this query UNION ALL.

This is my error:
[Err] 42000 - [SQL Server] ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Please check my code below.

If you have link for similar task, please give it me.
Can you explain any one or any sample code related this.

Your help would be very appreciated.
Thanks in advance for your time and hints.

Cheers.

SELECT
        [MAT],
        [NUMBER]
FROM
        TestTable
UNION ALL
        SELECT
                COALESCE ([MAT], 'Tot') AS [MAT],
                SUM ([NUMBER])
        FROM
                TestTable
        GROUP BY
                ROLLUP ([MAT])
        ORDER BY
                CASE ([MAT])
        WHEN 'mol' THEN
                1
        WHEN 'lip' THEN
                2
        WHEN 'mre' THEN
                3
        WHEN 'irt' THEN
                4
        WHEN 'pmc' THEN
                5
        WHEN 'mal' THEN
                6
        WHEN 'ras' THEN
                7
        WHEN 'uot' THEN
                8
        WHEN 'lac' THEN
                9
        WHEN 'bup' THEN
                10
        ELSE
                11
        END;
2

your ORDER BY clause contains only one item, the CASE expression

however, that item is not included in the SELECT clause

hence the error message – “ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.”

3

thanks a lot!


SELECT
	[MAT],
	[NUMBER]
FROM
	(
		SELECT
			[MAT],
			[NUMBER]
		FROM
			TestTable
		UNION
			SELECT
				COALESCE ([MAT], 'Tot') AS [MAT],
				SUM ([NUMBER])
			FROM
				TestTable
			GROUP BY
				ROLLUP ([MAT])
	) subQ
ORDER BY
	CASE ([MAT])
WHEN 'mol' THEN
	1
WHEN 'lip' THEN
	2
WHEN 'mre' THEN
	3
WHEN 'irt' THEN
	4
WHEN 'pmc' THEN
	5
WHEN 'mal' THEN
	6
WHEN 'ras' THEN
	7
WHEN 'uot' THEN
	8
WHEN 'lac' THEN
	9
WHEN 'bup' THEN
	10
ELSE
	11
END;