Combining UNION ALL and ORDER BY in Firebird

sql database database-design firebird
Chris picture Chris · Dec 9, 2008 · Viewed 42k times · Source

This is my first attempt at answering my own question, since someone may well run into this and so it might be of help. Using Firebird, I want to combine the results of two queries using UNION ALL, then sort the resulting output on a given column. Something like:

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

The parentheses came from valid syntax for other databases, and are needed to make sure the arguments to UNION ALL (an operation that's defined to work on tables - i.e. an unordered set of records) don't try to be ordered individually. However I couldn't get this syntax to work in Firebird - how can it be done?

Answer

Cade Roux picture Cade Roux · Dec 9, 2008 
SELECT C1, C2, C3
FROM (
    select C1, C2, C3 from T1
    union all 
    select C1, C2, C3 from T2
)
order by C3

Related questions

How to create a new schema/new user in Oracle Database 11g?

I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the DDL file. I have installed Oracle database 11g Express …

Read More
SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

If I have two relations in a database, like this: CREATE TABLE Courses ( CourseID int NOT NULL PRIMARY KEY, Course VARCHAR(63) NOT NULL UNIQUE, Code CHAR(4) NOT NULL UNIQUE ); CREATE TABLE BookCourses ( EntryID int NOT NULL PRIMARY KEY, BookID int …

Read More
A beginner's guide to SQL database design

Do you know a good source to learn how to design SQL solutions? Beyond the basic language syntax, I'm looking for something to help me understand: What tables to build and how to link them How to design for different …

Read More