SQL Query - Using Order By in UNION

Question

How can one programmatically sort a union query when pulling data from two tables  For example   SELECT table1 field1 FROM table1 ORDER BY table1 field1 UNION SELECT table2 field1 FROM table2 ORDER BY table2 field1   Throws an exception  Note  this is being attempted on MS Access Jet database engine

User · Answer

The second table cannot include the table name in the ORDER BY clause.

So...

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY field1

Does not throw an exception

User · Answer

Browsing this comment section I came accross two different patterns answering the question  Sadly for SQL 2012  the second pattern doesn t work  so here s my  work around     Order By on a Common Column  This is the easiest case you can encounter  Like many user pointed out  all you really need to do is add an Order By at the end of the query  SELECT a FROM table1 UNION SELECT a FROM table2 ORDER BY field1   or  SELECT a FROM table1 ORDER BY field1 UNION SELECT a FROM table2 ORDER BY field1     Order By on Different Columns  Here s where it actually gets tricky  Using SQL 2012  I tried the top post and it doesn t work   SELECT   FROM      SELECT table1 field1 FROM table1 ORDER BY table1 field1   DUMMY ALIAS1  UNION ALL  SELECT   FROM      SELECT table2 field1 FROM table2 ORDER BY table2 field1   DUMMY ALIAS2   Following the recommandation in the comment I tried this  SELECT   FROM      SELECT TOP 100 PERCENT table1 field1 FROM table1 ORDER BY table1 field1   DUMMY ALIAS1  UNION ALL  SELECT   FROM      SELECT TOP 100 PERCENT table2 field1 FROM table2 ORDER BY table2 field1   DUMMY ALIAS2   This code did compile but the DUMMY ALIAS1 and DUMMY ALIAS2 override the Order By established in the Select statement which makes this unusable   The only solution that I could think of  that worked for me was not using a union and instead making the queries run individually and then dealing with them  So basically  not using a Union when you want to Order By

User · Answer

For Sql Server 2014 2012 Others Not Checked     SELECT   FROM      SELECT table1 field1 FROM table1 ORDER BY table1 field1    as DUMMY ALIAS1  UNION ALL  SELECT   FROM      SELECT table2 field1 FROM table2 ORDER BY table2 field1    as DUMMY ALIAS2

User · Answer

I think this does a good job of explaining   The following is a UNION query that uses an ORDER BY clause   select supplier id  supplier name from suppliers where supplier id  gt  2000 UNION select company id  company name from companies where company id  gt  1000 ORDER BY 2    Since the column names are different between the two  select  statements  it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set    In this example  we ve sorted the results by supplier name   company name in ascending order  as denoted by the  ORDER BY 2    The supplier name   company name fields are in position  2 in the result set   Taken from here  http   www techonthenet com sql union php

User · Answer

By using order separately each subset gets order  but not the whole set  which is what you would want uniting two tables   You should use something like this to have one ordered set   SELECT TOP  100  PERCENT field1  field2  field3  field4  field5 FROM   SELECT table1 field1  table1 field2  table1 field3  table1 field4  table1 field5 FROM table1 UNION ALL  SELECT table2 field1  table2 field2  table2 field3  table2 field4  table2 field5 FROM  table2   AS unitedTables ORDER BY field5 DESC

User · Answer

The second table cannot include the table name in the ORDER BY clause.

So...

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY field1

Does not throw an exception

User · Answer

Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION   In this case  SELECT   FROM      SELECT table1 field1 FROM table1 ORDER BY table1 field1   DUMMY ALIAS1  UNION ALL  SELECT   FROM      SELECT table2 field1 FROM table2 ORDER BY table2 field1   DUMMY ALIAS2

User · Answer

This is the stupidest thing I ve ever seen  but it works  and you can t argue with results   SELECT   FROM       SELECT table1 field1 FROM table1 ORDER BY table1 field1     UNION     SELECT table2 field1 FROM table2 ORDER BY table2 field1   derivedTable   The interior of the derived table will not execute on its own  but as a derived table works perfectly fine  I ve tried this on SS 2000  SS 2005  SS 2008 R2  and all three work

User · Answer

SELECT FIELD1 AS NEWFIELD FROM TABLE1 ORDER BY FIELD1  UNION  SELECT FIELD2 FROM TABLE2 ORDER BY FIELD2  UNION  SELECT FIELD3 FROM TABLE3 ORDER BY FIELD3  ORDER BY NEWFIELD   Try this  It worked for me

User · Answer

SELECT field1 FROM table1 UNION SELECT field1 FROM table2 ORDER BY field1

User · Answer

Browsing this comment section I came accross two different patterns answering the question  Sadly for SQL 2012  the second pattern doesn t work  so here s my  work around     Order By on a Common Column  This is the easiest case you can encounter  Like many user pointed out  all you really need to do is add an Order By at the end of the query  SELECT a FROM table1 UNION SELECT a FROM table2 ORDER BY field1   or  SELECT a FROM table1 ORDER BY field1 UNION SELECT a FROM table2 ORDER BY field1     Order By on Different Columns  Here s where it actually gets tricky  Using SQL 2012  I tried the top post and it doesn t work   SELECT   FROM      SELECT table1 field1 FROM table1 ORDER BY table1 field1   DUMMY ALIAS1  UNION ALL  SELECT   FROM      SELECT table2 field1 FROM table2 ORDER BY table2 field1   DUMMY ALIAS2   Following the recommandation in the comment I tried this  SELECT   FROM      SELECT TOP 100 PERCENT table1 field1 FROM table1 ORDER BY table1 field1   DUMMY ALIAS1  UNION ALL  SELECT   FROM      SELECT TOP 100 PERCENT table2 field1 FROM table2 ORDER BY table2 field1   DUMMY ALIAS2   This code did compile but the DUMMY ALIAS1 and DUMMY ALIAS2 override the Order By established in the Select statement which makes this unusable   The only solution that I could think of  that worked for me was not using a union and instead making the queries run individually and then dealing with them  So basically  not using a Union when you want to Order By

User · Answer

SELECT FIELD1 AS NEWFIELD FROM TABLE1 ORDER BY FIELD1  UNION  SELECT FIELD2 FROM TABLE2 ORDER BY FIELD2  UNION  SELECT FIELD3 FROM TABLE3 ORDER BY FIELD3  ORDER BY NEWFIELD   Try this  It worked for me

User · Answer

This is how it is done  select   from       select top 100 percent pointx  pointy from point      where pointtype   1      order by pointy  A union all select   from       select top 100 percent pointx  pointy from point      where pointtype   2      order by pointy desc  B

User · Answer

SELECT field1 FROM table1 UNION SELECT field1 FROM table2 ORDER BY field1

User · Answer

SELECT table1Column1 as col1 table1Column2 as col2     FROM table1 UNION      SELECT table2Column1 as col1  table1Column2 as col2          FROM table2   ORDER BY col1 ASC

User · Answer

SELECT field1 FROM table1 UNION SELECT field1 FROM table2 ORDER BY field1

User · Answer

SELECT table1 field1 FROM table1  UNION SELECT table2 field1 FROM table2  ORDER BY field1    Work   Remember think sets   Get the set you want using a union and then perform your operations on it

User · Answer

I think this does a good job of explaining   The following is a UNION query that uses an ORDER BY clause   select supplier id  supplier name from suppliers where supplier id  gt  2000 UNION select company id  company name from companies where company id  gt  1000 ORDER BY 2    Since the column names are different between the two  select  statements  it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set    In this example  we ve sorted the results by supplier name   company name in ascending order  as denoted by the  ORDER BY 2    The supplier name   company name fields are in position  2 in the result set   Taken from here  http   www techonthenet com sql union php

User · Answer

This is the stupidest thing I ve ever seen  but it works  and you can t argue with results   SELECT   FROM       SELECT table1 field1 FROM table1 ORDER BY table1 field1     UNION     SELECT table2 field1 FROM table2 ORDER BY table2 field1   derivedTable   The interior of the derived table will not execute on its own  but as a derived table works perfectly fine  I ve tried this on SS 2000  SS 2005  SS 2008 R2  and all three work

User · Answer

SELECT table1 field1 FROM table1  UNION SELECT table2 field1 FROM table2  ORDER BY field1    Work   Remember think sets   Get the set you want using a union and then perform your operations on it

User · Answer

SELECT table1 field1 FROM table1  UNION SELECT table2 field1 FROM table2  ORDER BY field1    Work   Remember think sets   Get the set you want using a union and then perform your operations on it

User · Answer

Using a concrete example   SELECT name FROM Folders ORDER BY name UNION SELECT name FROM Files ORDER BY name   Files   name                               RTS exe thiny1 etl thing2 elt f txt tcpdump trial license  1  zip   Folders   name                              Contacts Desktop Downloads Links Favorites My Documents   Desired Output   results of first select first  i e  folders first   Contacts Desktop Downloads Favorites Links My Documents f txt RTMS exe tcpdump trial license  1  zip thiny1 etl thing2 elt   SQL to achieve the desired results   SELECT name  FROM       SELECT 1 AS rank  name FROM Folders     UNION      SELECT 2 AS rank  name FROM Files  dt ORDER BY rank  name

User · Answer

SELECT table1Column1 as col1 table1Column2 as col2     FROM table1 UNION      SELECT table2Column1 as col1  table1Column2 as col2          FROM table2   ORDER BY col1 ASC

User · Answer

This is how it is done  select   from       select top 100 percent pointx  pointy from point      where pointtype   1      order by pointy  A union all select   from       select top 100 percent pointx  pointy from point      where pointtype   2      order by pointy desc  B

User · Answer

Using a concrete example   SELECT name FROM Folders ORDER BY name UNION SELECT name FROM Files ORDER BY name   Files   name                               RTS exe thiny1 etl thing2 elt f txt tcpdump trial license  1  zip   Folders   name                              Contacts Desktop Downloads Links Favorites My Documents   Desired Output   results of first select first  i e  folders first   Contacts Desktop Downloads Favorites Links My Documents f txt RTMS exe tcpdump trial license  1  zip thiny1 etl thing2 elt   SQL to achieve the desired results   SELECT name  FROM       SELECT 1 AS rank  name FROM Folders     UNION      SELECT 2 AS rank  name FROM Files  dt ORDER BY rank  name

User · Answer

Here s an example from Northwind 2007   SELECT  Product ID    Order Date    Company Name    Transaction    Quantity  FROM  Product Orders  UNION SELECT  Product ID    Creation Date    Company Name    Transaction    Quantity  FROM  Product Purchases  ORDER BY  Order Date  DESC    The ORDER BY clause just needs to be the last statement  after you ve done all your unioning   You can union several sets together  then put an ORDER BY clause after the last set

User · Answer

The second table cannot include the table name in the ORDER BY clause.

So...

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY field1

Does not throw an exception

User · Answer

SELECT field1 FROM table1 UNION SELECT field1 FROM table2 ORDER BY field1

User · Answer

SELECT table1 field1 FROM table1  UNION SELECT table2 field1 FROM table2  ORDER BY field1    Work   Remember think sets   Get the set you want using a union and then perform your operations on it

User · Answer

SELECT field1 FROM   SELECT field1 FROM table1        UNION        SELECT field1 FROM table2        AS TBL ORDER BY TBL field1    use ALIAS

User · Answer

For Sql Server 2014 2012 Others Not Checked     SELECT   FROM      SELECT table1 field1 FROM table1 ORDER BY table1 field1    as DUMMY ALIAS1  UNION ALL  SELECT   FROM      SELECT table2 field1 FROM table2 ORDER BY table2 field1    as DUMMY ALIAS2

User · Answer

I think this does a good job of explaining   The following is a UNION query that uses an ORDER BY clause   select supplier id  supplier name from suppliers where supplier id  gt  2000 UNION select company id  company name from companies where company id  gt  1000 ORDER BY 2    Since the column names are different between the two  select  statements  it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set    In this example  we ve sorted the results by supplier name   company name in ascending order  as denoted by the  ORDER BY 2    The supplier name   company name fields are in position  2 in the result set   Taken from here  http   www techonthenet com sql union php

User · Answer

Here s an example from Northwind 2007   SELECT  Product ID    Order Date    Company Name    Transaction    Quantity  FROM  Product Orders  UNION SELECT  Product ID    Creation Date    Company Name    Transaction    Quantity  FROM  Product Purchases  ORDER BY  Order Date  DESC    The ORDER BY clause just needs to be the last statement  after you ve done all your unioning   You can union several sets together  then put an ORDER BY clause after the last set

User · Answer

SELECT table1Column1 as col1 table1Column2 as col2     FROM table1 UNION      SELECT table2Column1 as col1  table1Column2 as col2          FROM table2   ORDER BY col1 ASC

User · Answer

If necessary to keep the inner sorting   SELECT 1 as type  field1 FROM table1  UNION  SELECT 2 as type  field1 FROM table2  ORDER BY type  field1

User · Answer

SELECT table1Column1 as col1 table1Column2 as col2     FROM table1 UNION      SELECT table2Column1 as col1  table1Column2 as col2          FROM table2   ORDER BY col1 ASC

User · Answer

Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION   In this case  SELECT   FROM      SELECT table1 field1 FROM table1 ORDER BY table1 field1   DUMMY ALIAS1  UNION ALL  SELECT   FROM      SELECT table2 field1 FROM table2 ORDER BY table2 field1   DUMMY ALIAS2

User · Answer

If necessary to keep the inner sorting   SELECT 1 as type  field1 FROM table1  UNION  SELECT 2 as type  field1 FROM table2  ORDER BY type  field1

User · Answer

The second table cannot include the table name in the ORDER BY clause.

So...

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY field1

Does not throw an exception

User · Answer

SELECT field1 FROM   SELECT field1 FROM table1        UNION        SELECT field1 FROM table2        AS TBL ORDER BY TBL field1    use ALIAS

User · Answer

By using order separately each subset gets order  but not the whole set  which is what you would want uniting two tables   You should use something like this to have one ordered set   SELECT TOP  100  PERCENT field1  field2  field3  field4  field5 FROM   SELECT table1 field1  table1 field2  table1 field3  table1 field4  table1 field5 FROM table1 UNION ALL  SELECT table2 field1  table2 field2  table2 field3  table2 field4  table2 field5 FROM  table2   AS unitedTables ORDER BY field5 DESC

User · Answer

Here s an example from Northwind 2007   SELECT  Product ID    Order Date    Company Name    Transaction    Quantity  FROM  Product Orders  UNION SELECT  Product ID    Creation Date    Company Name    Transaction    Quantity  FROM  Product Purchases  ORDER BY  Order Date  DESC    The ORDER BY clause just needs to be the last statement  after you ve done all your unioning   You can union several sets together  then put an ORDER BY clause after the last set

User · Answer

I think this does a good job of explaining   The following is a UNION query that uses an ORDER BY clause   select supplier id  supplier name from suppliers where supplier id  gt  2000 UNION select company id  company name from companies where company id  gt  1000 ORDER BY 2    Since the column names are different between the two  select  statements  it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set    In this example  we ve sorted the results by supplier name   company name in ascending order  as denoted by the  ORDER BY 2    The supplier name   company name fields are in position  2 in the result set   Taken from here  http   www techonthenet com sql union php

User · Answer

Here s an example from Northwind 2007   SELECT  Product ID    Order Date    Company Name    Transaction    Quantity  FROM  Product Orders  UNION SELECT  Product ID    Creation Date    Company Name    Transaction    Quantity  FROM  Product Purchases  ORDER BY  Order Date  DESC    The ORDER BY clause just needs to be the last statement  after you ve done all your unioning   You can union several sets together  then put an ORDER BY clause after the last set

[sql] SQL Query - Using Order By in UNION

Examples related to sql

Examples related to ms-access

Examples related to sorting

Examples related to sql-order-by

Examples related to union