mySQL query to return last record for each table

Question

I have a mySQl db (name "stocks") with 50 tables, each tables with

id, symbol, date, time, open, high, low, close, volume as columns (9 columns).

I would like to know what is the last record for each table, ordered for date then time.

Should I have to ORDER BY all data for each table or there is a better way to just know last record?

I am asking help for a query that just return only last record for each table in db.

Thanks

PS For last record I mean most recent as Date then Time

Answer 1

There are two options how to do that:

-- I would use this only if you need more than one records
SELECT * FROM table ORDER BY date DESC LIMIT 1;

-- Way to go:
SELECT * FROM table WHERE date = (SELECT MAX(date) FROM table) LIMIT 1;

Don't forget to add index on date. If it's possible you add lot's of records at the same time you will have to add:

ORDER BY id DESC -- In case that date is highest for records for last records
ORDER BY time DESC -- Every other case

To the end of query

Answer 2

I am going to make the assumption that the record with the largest ID is the "last" (assuming strictly increasing sequential IDs that are unique within a table). If you have a better definition of "last" that could make a difference.

To get one "last" record, you could do:

Select * from table_1 where id = (select max(id) from table_1);

To get the results of all 50 tables into a single result set, you could do:

Select * from table_1 where id = (select max(id) from table_1)
union
Select * from table_2 where id = (select max(id) from table_2)
union
Select * from table_3 where id = (select max(id) from table_3)
union...

---

A MySQL-specific solution could be

Select * from table_1 order by id desc limit 1
union
Select * from table_2 order by id desc limit 1
union
Select * from table_3 order by id desc limit 1
union...

---

Based on your edit (where you actually define what you mean by "last"):

Select * from table_1 order by date desc, time desc, id desc limit 1
union
Select * from table_2 order by date desc, time desc, id desc limit 1
union
Select * from table_3 order by date desc, time desc, id desc limit 1
union...