Skip to content
Advertisement

Pick the max value of id from multiple tables

I have 3 tables. Each of them have id column as PRIMARY.

There is no relation between tables.

Table1 has ids: 1, 2, 3, 4
Table2 has ids: 1, 3, 7
Table3 has ids: 4, 9, 10, 15, 20

I want to make a query which gives me the max id of every table.

table1_max_id | table2_max_id | table3_max_id
--------------|---------------|--------------
1             | 7             | 20   

What I have tried was this:

SELECT 
    MAX(table1.id) AS "table1_max_id",
    MAX(table2.id) AS "table2_max_id",
    MAX(table3.id) AS "table3_max_id",
FROM table1, table2, table3;

But I get NULL, NULL, NULL.

It is possible to done that? If yes, how?

Advertisement

Answer

MySQL syntax supports placing subqueries into the SELECT clause, so you could use:

SELECT
    (SELECT MAX(id) FROM table1) AS table1_max_id,
    (SELECT MAX(id) FROM table2) AS table2_max_id,
    (SELECT MAX(id) FROM table3) AS table3_max_id;
5 People found this is helpful
Advertisement