ORDER BY: multiple tables

[bgarlock]

I am trying to write a SELECT statement, which brings in information from 2 tables. I would like to be able to ORDER BY date fields from both tables.

For example:

If table a, and table b, both have a date field: a_date and b_date, I would like the results from both tables to ORDER BY both of the tables combined. So, if the result set included:

a_date: 2002-01-01
a_date: 2002-01-02
a_date: 2002-01-03
b_date: 2002-01-04
a_date: 2002-01-05
a_date: 2002-01-06

The b_date was ordered properly in this result set. Currently I have 2 select statements, and process the order in my scripting language (PHP) to obtain my desired result. I know there must be a more efficient way of using the SELECT statement to achieve this.

Thanks in advance for any insight.

- Bruce

Bruce Garlock
bruceg@tiac.net

http://bruceg.dyndns.org

 

[angiole]

I'm assuming here you are selecting these dates from the 2 tables using a UNION ALL statement.

If that's the case you may, depending on your DBMS, try the following:

Select   the_date
From     (
         Select date1 as the_date
         From   Table1
         Where ...
         UNION ALL
         Select date2 as the_date
         From   Table2
         Where ...
         )
Order by The_Date

This example, as you may notice, will remove the requirement of ordering the data externally, thereby saving time (hopefully).


AA 8~)

 

[r937]

well, if the scripting language is php, chances are the database is mysql, so the embedded union select won't work (mysql doesn't do subselects... yet)

mysql does let you specify the ORDER BY on the UNION, except that mysql doesn't support UNION prior to release 4.0, and then you have to put the selects into parentheses

  (select date1 as the_date
    from table1
   where ...)
 union [all]
  (select date2 as the_date
    from table2
   where ...)
order by the_date

see

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#UNION

ugly, eh?

rudy

http://rudy.ca/