order by and union all compatibility

[Former Member]

In this topic Merge Two resultsets from a Stored Procedure discussed query which has union all and order by clause in every subquery. Construction:

SELECT TOP 5 dt_joined, u_name from tbl_a order by dt_joined
UNION ALL 
SELECT TOP 5 dt_joined, u_name from tbl_b order by dt_joined

return syntax error.

My fault - SA don't support order by clause within every union all subquery. Construction:

select .... order by ..
union all
select ... order by ..

is wrong.

but construction:

select from (select ... order by ...)
union all
select from (select ... order by ...)

is ok.

For my point of view, every query block must be logically independent from other blocks, but SA don't agree with me 🙂

SA10, documentation don't describe this behavior (or I am don't find it).

Please can you explain this situations with order by and union all and may be add some more info into docs. Thank's

[Former Member · Accepted solution]

In this case, you need to enclose the query blocks in parentheses:

(select top 5 row_num from rowgenerator order by row_num)
union all
(select top 5 -row_num from rowgenerator order by row_num)
order by row_num

The problem is that the UNION statement supports ORDER BY with the interpretation that the entire result set of the UNION is ordered. In order to indicate that the ORDER BY is associated with the query block, not the entire UNION, you need to use parentheses.

I will forward a request to add some description to the documentation for UNION.

[Breck_Carter]

It is a good question, and given the state of the SQL Anywhere Help, it is a VERY GOOD question.

Semantically, what you want to write, and what you CAN write, are the same... same results, different syntax.

For some reason, the BNF syntax for SQL Anywhere is not publicly documented... which is a shame, because that would prevent confusion like the following.

---

These two SQL Anywhere 12 Help topics explicitly (and incorrectly) state that you CAN write a UNION between two instances of "a query expression with an ORDER BY clause"

UNION statement

[ WITH temporary-views ] query-block
  UNION [ ALL | DISTINCT ] query-block
  [ ORDER BY [ integer | select-list-expression-name ] [ ASC | DESC ], ... ]
  [ FOR XML xml-mode ]
  [ OPTION( query-hint, ... ) ]

query-block : see Common elements in SQL syntax

Common elements in SQL syntax

query-block   A query block is a simple query expression, or a query expression with an 
ORDER BY clause.

query-expression   A query expression can be a SELECT, UNION, INTERSECT, or EXCEPT block 
(that is, a statement that does not contain an ORDER BY, WITH, FOR, FOR XML, or OPTION 
clause), or any combination of such blocks.

That is just not true, and AFAIK it never has been true in the history of SQL... the UNION itself can have one ORDER BY at the end of all the outer SELECTs. Of course, modern RDBMS's allow ORDER BY on ( query expressions inside parentheses ), and that gives the functionality everyone wants.

---

Here's the Microsoft SQL Server 2008 syntax which DOES use BNF to keep everything clear and simple; it uses different terms "query_expression" and "query_specification" to show which one needs ( parentheses ) and which one doesn't, and where:

SELECT (Transact-SQL)

<select statement=""> ::=

[WITH <common_table_expression> [,...n]]
    <query_expression> 
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] } 
  [ ,...n ] ] 
    [ COMPUTE 
  { { AVG | COUNT | MAX | MIN | SUM } (expression )} [ ,...n ] 
  [ BY expression [ ,...n ] ] 
    ] 
    [ <for clause="">] 
    [ OPTION ( <query_hint> [ ,...n ] ) ]

<query_expression> ::= 
    { <query_specification> | ( <query_expression> ) } 
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }
        <query_specification> | ( <query_expression> ) [...n ] ]

<query_specification> ::= 
SELECT [ ALL | DISTINCT ] 
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]

< select_list > 
    [ INTO new_table ] 
    [ FROM { <table_source> } [ ,...n ] ] 
    [ WHERE <search_condition> ] 
    [ <group by=""> ] 
    [ HAVING < search_condition > ] 

UNION (Transact-SQL) gives this example:

/* INCORRECT */
USE AdventureWorks2008R2;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO

/* CORRECT */
USE AdventureWorks2008R2;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

Perfection In Documentation would be a combination of well-laid out BNF syntax accompanied by a long series of simple examples... not unlike the above. As Uncle Ben might have said, with crappy syntax comes great documentation 🙂

---

Here are some other references:

IBM DB2: Using the UNION keyword to combine subselects

MySQL: 12.2.8.3. UNION Syntax

The MySQL syntax for ( using parentheses with ORDER BY ) is interesting:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Does that work in SQL Anywhere? Hmmm... why YES, it does! Woohoo!

CREATE TABLE tbl_class_a
(
u_name varchar(20),
dt_joined date
);
CREATE TABLE tbl_class_b
(
u_name varchar(20),
dt_joined date
);

INSERT tbl_class_a VALUES ( 'a1', CURRENT DATE );
INSERT tbl_class_a VALUES ( 'a2', CURRENT DATE + 1 );
INSERT tbl_class_a VALUES ( 'a3', CURRENT DATE + 2 );
INSERT tbl_class_b VALUES ( 'b1', CURRENT DATE );
INSERT tbl_class_b VALUES ( 'b2', CURRENT DATE + 1 );
INSERT tbl_class_b VALUES ( 'b3', CURRENT DATE + 2 );
INSERT tbl_class_b VALUES ( 'b4', CURRENT DATE + 3 );
INSERT tbl_class_b VALUES ( 'b5', CURRENT DATE + 4 );
INSERT tbl_class_b VALUES ( 'b6', CURRENT DATE + 5 );
COMMIT;

SELECT TOP 5 *
  FROM ( SELECT TOP 5 u_name, dt_joined
           FROM ( ( SELECT TOP 5 'a' AS source, * FROM tbl_class_a ORDER BY dt_joined ) 
                  UNION ALL
                  ( SELECT TOP 5 'b' AS source, * FROM tbl_class_b ORDER BY dt_joined ) ) AS ab
          ORDER BY source, dt_joined ) AS top5ab
 ORDER BY dt_joined, u_name;

u_name,dt_joined
'a1','2011-08-05'
'b1','2011-08-05'
'a2','2011-08-06'
'b2','2011-08-06'
'a3','2011-08-07'

Wow! Uncle Ben was RIGHT!

[Breck_Carter]

...and that is why it is important to have BOTH accurate syntax (BNF) and good examples. The BNF for SQL Server 2008 should have told me what the MySQL example finally did tell me. Bottom line: dumbing down the syntax is NOT THE ANSWER, examples are the answer.