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.
Code:
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;