ALLBASE/SQL Reference Manual (36216-90216)

118 Chapter3
SQL Queries
Complex Queries
ORDER BY PartNumber
----------------
PARTNUMBER
----------------
1123-P-01
1133-P-01
1143-P-01
1153-P-01
1223-MU-01
1233-MU-01
1323-D-01
1333-D-01
1343-D-01
1523-K-01
1623-TD-01
1823-PT-01
Note that no rows are duplicated. When the UNION statement is not qualified by the ALL
statement, all duplicate rows are removed from the query result. Notice that the ORDER BY
clause must be at the
end of the SELECT statement. It cannot be included in the separate
query expressions that make up the overall statement. Only the final query result can be
ordered.
If the UNION ALL statement is used in the previous query, the result can contain duplicate
rows. The following example flags duplicate rows with two types of arrows that are
described below:
----------------
PARTNUMBER
----------------
1123-P-01
1123-P-01 <----
1123-P-01 <---+
1133-P-01
1133-P-01 <---+
1143-P-01
1143-P-01 <----
1153-P-01
1153-P-01 <---+
1223-MU-01
1233-MU-01 <----
1323-D-01
1333-D-01
1343-D-01
1523-K-01
1623-TD-01
1823-PT-01
In the above example, rows are duplicated for the following:
• More than one vendor supplies some parts (these duplicates are indicated by <----)
• Vendor 9002 supplies some parts that take 30 or more days to deliver (these duplicates
are indicated by <---+)