Combining Union All and Order by in Firebird

Combining UNION ALL and ORDER BY in Firebird

SELECT C1, C2, C3
FROM (
    select C1, C2, C3 from T1
    union all 
    select C1, C2, C3 from T2
)
order by C3

How to use order by with union all in sql?

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 

How to use ORDER BY inside UNION

Something like this should work in MySQL:

SELECT a.*
  FROM ( 
         SELECT ...  FROM ... ORDER BY ... 
       ) a
 UNION ALL 
SELECT b.*
  FROM ( 
         SELECT ...  FROM ... ORDER BY ... 
       ) b

to return rows in an order we'd like them returned. i.e. MySQL seems to honor the ORDER BY clauses inside the inline views.

But, without an ORDER BY clause on the outermost query, the order that the rows are returned is not guaranteed.

If we need the rows returned in a particular sequence, we can include an ORDER BY on the outermost query. In a lot of use cases, we can just use an ORDER BY on the outermost query to satisfy the results.

But when we have a use case where we need all the rows from the first query returned before all the rows from the second query, one option is to include an extra discriminator column in each of the queries. For example, add ,'a' AS src in the first query, ,'b' AS src to the second query.

Then the outermost query could include ORDER BY src, name, to guarantee the sequence of the results.

---

FOLLOWUP

In your original query, the ORDER BY in your queries is discarded by the optimizer; since there is no ORDER BY applied to the outer query, MySQL is free to return the rows in whatever order it wants.

The "trick" in query in my answer (above) is dependent on behavior that may be specific to some versions of MySQL.

Test case:

populate tables

CREATE TABLE foo2 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;
CREATE TABLE foo3 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;

INSERT INTO foo2 (id, role) VALUES 
  (1,'sam'),(2,'frodo'),(3,'aragorn'),(4,'pippin'),(5,'gandalf');
INSERT INTO foo3 (id, role) VALUES 
  (1,'gimli'),(2,'boromir'),(3,'elron'),(4,'merry'),(5,'legolas');

query

SELECT a.*
  FROM ( SELECT s.id, s.role
           FROM foo2 s
          ORDER BY s.role
       ) a
 UNION ALL
SELECT b.*
  FROM ( SELECT t.id, t.role
           FROM foo3 t
          ORDER BY t.role
       ) b

resultset returned

    id  role     
 ------  ---------
      3  aragorn  
      2  frodo    
      5  gandalf  
      4  pippin   
      1  sam      
      2  boromir  
      3  elron    
      1  gimli    
      5  legolas  
      4  merry    

The rows from foo2 are returned "in order", followed by the rows from foo3, again, "in order".

Note (again) that this behavior is NOT guaranteed. (The behavior we observer is a side effect of how MySQL processes inline views (derived tables). This behavior may be different in versions after 5.5.)

If you need the rows returned in a particular order, then specify an ORDER BY clause for the outermost query. And that ordering will apply to the entire resultset.

As I mentioned earlier, if I needed the rows from the first query first, followed by the second query, I would include a "discriminator" column in each query, and then include the "discriminator" column in the ORDER BY clause. I would also do away with the inline views, and do something like this:

SELECT s.id, s.role, 's' AS src
  FROM foo2 s
 UNION ALL
SELECT t.id, t.role, 't' AS src
  FROM foo3 t
 ORDER BY src, role

What is the difference between UNION and UNION ALL?

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE)

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)

Firebird performance on where and order by clause

Firebird use an index if available for >, <, >=, <= and between operators.

I made a test before posting this with this table:

SQL> show table stock2;
ITEMID                          INTEGER Not Null
STOCKDATE                       TIMESTAMP Not Null
LASTCOST                        DOUBLE PRECISION Nullable

populated it with some data (not millions per month, but enough to test performance)

SQL> select extract(year from stockdate),
CON>        extract(month from stockdate), count(*)
CON>   from stock2
CON>  group by 1, 2
CON>  order by 1, 2;

EXTRACT EXTRACT        COUNT
======= ======= ============
   2012       1       706473
   2012       2       628924
   2012       3       670038
   2012       4       649411
   2012       5       671512
   2012       6       648878
   2012       7       671182
   2012       8       671212
   2012       9       649312
   2012      10       671881
   2012      11       648815
   2012      12       671579

I ran your query, first without any index (takes seconds), then indexing only the itemid column, proving a better plan and better performance, and finally using a index by itemid and date, where the performance is much better. The show plan allows you see the engine uses the index by default.

SQL> set plan on;
SQL>
SQL> select first 1 lastcost
CON>   from stock2
CON>  where itemid = 127
CON>    and stockdate > '2012-01-15'
CON>    and stockdate < '2012-03-27'
CON> order by stockdate desc;

PLAN SORT ((STOCK2 INDEX (IDX_STOCK2IDDATE)))

               LASTCOST
=======================
      149.7170031070709

SQL>

the index definitions I'm using are:

create index idx_stock2id on stock2 (itemid);
create index idx_stock2iddate on stock2 (itemid, stockdate);

Group By and Order By with UNION ALL

You could apply the group by and order by after the union all:

SELECT  (SUM(addition)) AS [COUNT], MAX([date]) AS [max_date]
FROM    (SELECT addition, CONVERT(VARCHAR(12),CREATED,102)) as [date]
         FROM   [TABLE_ONE]
         WHERE  CONVERT(VARCHAR(12),CREATED,102) 
                    BETWEEN CONVERT(date,@startdate) AND 
                            CONVERT(date,@enddate)
                AND [ServiceID]=@serid 
         UNION ALL
         SELECT addition, (CONVERT(VARCHAR(12),CREATED,102)) as [date]
         FROM   [TABLE_TWO]
         WHERE  CONVERT(VARCHAR(12),CREATED,102) 
                    BETWEEN CONVERT(date,@startdate) AND 
                            CONVERT(date,@enddate)
                AND [ServiceID]=@serid) t
GROUP BY [date]
ORDER BY 2

Merging multiple related firebird select procedure using If else or case method

You may try a construct like this:

WITH
  Q_2 as (
      SELECT
        AP_PSTIONLVL_NON,
        AP_POSTION_NON,
        RANKING_MONTH,
        RANKING_YEAR
      FROM APPLICANT
      WHERE RANKING_MONTH = :M
        AND RANKING_YEAR = :Y
      GROUP BY
        AP_PSTIONLVL_NON,
        AP_POSTION_NON,
        RANKING_MONTH,
        RANKING_YEAR
  ), 
  Q_1 as (
     SELECT
       '',
       '',
       RANKING_MONTH,
       RANKING_YEAR
     FROM APPLICANT
     WHERE RANKING_MONTH = :M 
       AND RANKING_YEAR = :Y
     GROUP BY
       RANKING_MONTH,
       RANKING_YEAR
 )

SELECT * FROM Q_2 WHERE :REFERENCE=2
   UNION ALL
SELECT * FROM Q_1 WHERE :REFERENCE=1

Things to notice:

• Making a stored procedure which contains nothing but a single SELECT is bad idea in Firebird/Interbase. It will disable SQL optimizer job on joining, and will add nothing of value. Firebird is not MS SQL.
• The second query was set first in the unioned chain, because it is the first query where UNION takes column names and data types from. Would Q_1 go first - the two anonymous columns would be a problem.
• Some database access libraries might have problems with several parameters in a query having same names. You might need to rename :M into :M_1 and :M_2, etc. Or maybe not, test your library.
• Your specific programming language/library can have different SQL parameters syntax than :name used above. Especially, that Firebird itself on SQL level only supports unnamed parameters. Check your library documentation. You might also get insights reading code examples at https://bobby-tables.com/
• Read documentation about UNION and CTE (Common Table Expressions) at https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html

---

Related Topics