How to execute UNION without sorting SQL

Question

UNION joins two results and remove duplicates  while UNION ALL does not remove duplicates  UNION also sort the final output   What I want is the UNION ALL without duplicates and without the sort  Is that possible   The reason for this is that I want the result of the first query to be on top of the final result  and the second query at the bottom  and each sorted as if they where run individually

User · Answer

1 1   select 1 from dual union all select 1 from dual  1   select 1 from dual union select 1 from dual

User · Answer

I notice this question gets quite a lot of views so I ll first address a question you didn t ask   Regarding the title  To achieve a  Sql Union All with    distinct     then simply replace UNION ALL with UNION  This has the effect of removing duplicates   For your specific question  given the clarification  The first query should have  priority   so duplicates should be removed from bottom  you can use  SELECT col1         col2         MIN grp  AS source group FROM    SELECT 1 AS grp                 col1                 col2         FROM   t1         UNION ALL         SELECT 2 AS grp                 col1                 col2         FROM   t2  AS t GROUP  BY col1            col2 ORDER  BY MIN grp             col1

User · Answer

The sort is used to eliminate the duplicates  and is implicit for DISTINCT and UNION queries  but not UNION ALL  - you could still specify the columns you d prefer to order by if you need them sorted by specific columns   For example  if you wanted to sort by the result sets  you could introduce an additional column  and sort by that first   SELECT foo  bar  1 as ResultSet FROM Foo WHERE bar   1 UNION SELECT foo  bar  2 as ResultSet FROM Foo WHERE bar   3 UNION SELECT foo  bar  3 as ResultSet FROM Foo WHERE bar   2 ORDER BY ResultSet

User · Answer

Consider these tables  Standard SQL code  runs on SQL Server 2008    WITH A       AS               SELECT            FROM                 VALUES  1                          2                          3                          4                          5                          6                  AS T  col               B       AS               SELECT            FROM                 VALUES  9                          8                          7                          6                          5                          4                  AS T  col                The desired effect is this to sort table A by col ascending  sort table B by col descending then unioning the two  removing duplicates  retaining order before the union and leaving table A results on the  top  with table B on the  bottom  e g   pesudo code       SELECT      FROM A   ORDER       BY col   UNION    SELECT      FROM B   ORDER       BY col DESC      Of course  this won t work in SQL because there can only be one ORDER BY clause and it can only be applied to the top level table expression  or whatever the output of a SELECT query is known as  I call it the  resultset     The first thing to address is the intersection between the two tables  in this case the values 4  5 and 6  How the intersection should be sorted needs to be specified in SQL code  therefore it is desirable that  the designer specifies this too   i e  the person asking the question  in this case     The implication in this case would seem to be that the intersection   duplicates   should be sorted within the results for table A  Therefore  the sorted  resultset should look like this         VALUES  1   -- A including intersection  ascending               2   -- A including intersection  ascending               3   -- A including intersection  ascending               4   -- A including intersection  ascending               5   -- A including intersection  ascending               6   -- A including intersection  ascending               9   -- B only  descending                8   -- B only  descending                 7   -- B only  descending    Note in SQL  top  and  bottom  has no inferent meaning and a table  other than a resultset  has no inherent ordering  Also  to cut a long story short  consider that UNION removes duplicate rows by implication and must be applied before ORDER BY  The conclusion has to be that each table s sort order must be explicitly defined by exposing a sort order column s  before being unioned  For this we can use the ROW NUMBER   windowed function e g                  A ranked      AS              SELECT col                ROW NUMBER   OVER  ORDER BY col  AS sort order 1         FROM A                      -- include the intersection              B ranked      AS              SELECT                  ROW NUMBER   OVER  ORDER BY col DESC  AS sort order 1         FROM B        WHERE NOT EXISTS             -- exclude the intersection                          SELECT                               FROM A                           WHERE A col   B col                                   SELECT    1 AS sort order 0    FROM A ranked UNION SELECT    2 AS sort order 0    FROM B ranked ORDER BY sort order 0  sort order 1

User · Answer

UNION also sort the final output  - only as an implementation artifact  It is by no means guaranteed to perform the sort  and if you need a particular sort order  you should specify it with an ORDER BY clause  Otherwise  the output order is whatever is most convenient for the server to provide   As such  your request for a function that performs a UNION ALL but that removes duplicates is easy - it s called UNION     From your clarification  you also appear to believe that a UNION ALL will return all of the results from the first query before the results of the subsequent queries  This is also not guaranteed  Again  the only way to achieve a particular order is to specify it using an ORDER BY clause

User · Answer

Try this     SELECT DISTINCT   FROM          SELECT  column1  column2 FROM Table1       UNION ALL       SELECT  column1  column2 FROM Table2       UNION ALL       SELECT  column1  column2 FROM Table3      X ORDER BY Column1

User · Answer

SELECT    1 AS sort order   FROM table1  EXCEPT  SELECT    1 AS sort order   FROM table2 UNION SELECT    1 AS sort order   FROM table1  INTERSECT  SELECT    1 AS sort order   FROM table2 UNION SELECT    2 AS sort order   FROM table2  EXCEPT  SELECT    2 AS sort order   FROM table1 ORDER BY sort order    But the real answer is  other than the ORDER BY clause  the sort order will by arbitrary and not guaranteed

User · Answer

select T Col1  T Col2  T Sort from              select T Col1               T Col2               T Sort               rank   over partition by T Col1  T Col2 order by T Sort  as rn       from                         select Col1  Col2  1 as Sort             from Table1             union all             select Col1  Col2  2             from Table2             as T       as T where T rn   1     order by T Sort

User · Answer

I assume your tables are table1 and table2 respectively  and your solution is    select   from table1 MINUS select   from table2  UNION ALL  select   from table2 MINUS select   from table1

User · Answer

You can do something like this   Select distinct name from   SELECT r name FROM outsider role mapping orm1      union all SELECT r name FROM user role mapping orm2   tmp

[sql] How to execute UNION without sorting? (SQL)

Examples related to sql

Examples related to union

Examples related to distinct

Examples related to union-all