Question 315376 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Looking to sort tables in a database based on the last time table was updated

I’m looking to sort multiple tables that are in a database based on the last time the table was updated. The tables all have a column called Update_Time that updates the timestamp when that row is updated in the table. I have tried with the UPDATE_TIME from the TABLE_SCHEMA, but that updates when any of the tables on my webpage gets updated. Below is what I have tried that doesn’t work. Any help would be greatly appreciated.

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'myDatabase' 
ORDER BY (
    SELECT MAX(`Update_Time`) AS "Last Updated" FROM `myTable`
);
Tags:

2

Answers


  1. 0

    First, your order by clause is using a date, not a column, to perform the sort, so every row is sorted with the same value which has no effect. For example, if the most recent update date is 2024-01-06 your query becomes:

    SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'myDatabase' 
ORDER BY '2024-01-06';

    Second, the schema information held by the database server is not the data it’s meta data about the tables. You would have to use some dynamic SQL to get the list of tables from the schema info then run queries to get the most recent update in each table, then rank the tables by that date.

    What do you actully need the most recent update for? There might be an easier way to do this if you tell us more about the problem you are actully trying to solve.

    In response to your comment about getting this working with the table design you have, you can use a union query if the number of tables (competition classes) is fixed.

    SELECT table_name, last_update FROM (
  SELECT `Results_Table_1` as table_name, MAX(UPDATE_TIME) 
  last_update FROM Results_Table_1
  UNION
  SELECT `Results_Table_2` as table_name, MAX(UPDATE_TIME) 
  last_update FROM Results_Table_2
  UNION
  SELECT ...
) all_tables
ORDER BY last_update DESC;
    Login or Signup to reply.
  2. 0

    Is this what you are looking for? Try to run this and in the output youll get the dynamic SQL for all the tables in union just add order by update_time at all and little bit syntax adjustment of the output sqls

    With tabs as 
(
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'myDatabase' 
)
Select 'Select '|| table_name ||', ' ||
update_time || ' union '|| char(10)  
From tabs ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search