How to use order by with union all in sql

Question

I tried the sql query given below    SELECT   FROM  SELECT    FROM TABLE A ORDER BY COLUMN 1 DUMMY TABLE UNION ALL  SELECT   FROM TABLE B    It results in the following error      The ORDER BY clause is invalid in views  inline functions  derived   tables  subqueries  and common table expressions  unless TOP or FOR   XML is also specified    I need to use order by in union all  How do I accomplish this

User · Accepted Answer

SELECT     FROM                        SELECT   FROM TABLE A              UNION ALL              SELECT   FROM TABLE B           dum -- ORDER BY         but if you want to have all records from Table A on the top of the result list  the you can add user define value which you can use for ordering   SELECT     FROM                        SELECT    1 sortby FROM TABLE A              UNION ALL              SELECT    2 sortby FROM TABLE B           dum ORDER   BY sortby

User · Answer

Not an OP direct response  but I thought I would jimmy in here responding to the the OP s ERROR messsage  which may point you in another direction entirely   All these answers are referring to an overall ORDER BY once the record set has been retrieved and you sort the lot   What if you want to ORDER BY each portion of the UNION independantly  and still have them  joined  in the same SELECT   SELECT pass1   FROM    SELECT TOP 1000 tblA ID  tblA CustomerName    FROM TABLE A AS tblA ORDER BY 2  AS pass1 UNION ALL  SELECT pass2   FROM     SELECT TOP 1000 tblB ID  tblB CustomerName     FROM TABLE B AS tblB ORDER BY 2  AS pass2   Note the TOP 1000 is an arbitary number  Use a big enough number to capture all of the data you require

User · Answer

You don t really need to have parenthesis  You can sort directly   SELECT    1 AS RN FROM TABLE A UNION ALL  SELECT    2 AS RN FROM TABLE B ORDER BY RN  COLUMN 1

User · Answer

Select  Shambhu  as ShambhuNewsFeed Note as  News Fedd  NotificationId from Notification with nolock  where DesignationId  Designation  Union All  Select  Shambhu  as ShambhuNewsFeed Note as  Notification  NotificationId from Notification with nolock   where DesignationId  Designation  order by NotificationId desc

User · Answer

select CONCAT Name      substr occupation  1  1        AS f1 from OCCUPATIONS union select temp str AS f1 from   select count occupation  AS counts  occupation  concat  There are a total of    count occupation        lower occupation   s    As str  from OCCUPATIONS group by occupation order by counts ASC  occupation ASC    As temp  order by f1

[sql] How to use order by with union all in sql?

Examples related to sql

Examples related to sql-server