SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [SQL Server] Use ORDER BY in query UNION

    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;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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."
    thanks a lot!
    Code:
    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;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •