zeka zeka - 1 month ago 10
MySQL Question

How to order tables in SQL by date

I have in database tables: table1, table2, table3... All of them have column date_table1, date_table2,..That columns have dates which are the same for all rows in that table, for example, table1 have column date_table1 that have date 2013-01-01, and it have that same date in all rows, table2 will have for example date 2013-01-02 and it will have only that date, that value. So, I need to sort my tables by date, i need to get list of the table names sorted from the earliest date until the latest. Can anyone help?

Answer

You can do something like this:

select tablename, thedate
from ((select 'table1' as tablename, max(date) as thedate from table1) union all
      (select 'table2' as tablename, max(date) as thedate from table2) union all
      (select 'table3' as tablename, max(date) as thedate from table3)
     ) t
order by thedate, tablename;

To add more tables, just add additional rows with the union all. If you have a lot of tables, but the names of the tables in a column in Excel and use Excel functions to generate the SQL code.

EDIT:

I actually like Matt's approach more (that he mentions in a comment):

select tablename, thedate
from ((select 'table1' as tablename, date as thedate from table1 limit 1) union all
      (select 'table2' as tablename, date as thedate from table2 limit 1) union all
      (select 'table3' as tablename, date as thedate from table3 limit 1)
     ) t
order by thedate, tablename;

Without indexes on table<n>(date), this will run much faster.

Comments