Get the maximum field value between different tables and the table from which it came

Question

I have many tables, say T1, T2, T3.

Every table has a column named field. I'd like to get the maximum value of field in the three tables, but also from which table it came, for example:

T1 { 6, 8, 23 }
T2 { 8, 45, 23 }
T3 { 68, 5, 67 }

I have to get 68 knowing that it belongs to table T3. I use MySQL as DBMS.

Answer 1

Here is the query

select * from 
(
    select 'T1' srctable,(select max(field) from T1) maxfield union
    select 'T2'         ,(select max(field) from T2) union
    select 'T3'         ,(select max(field) from T3)
) A WHERE maxfield = 
(
    select max(maxfield) from 
    (
        select 'T1' srctable,(select max(field) from T1) maxfield union
        select 'T2'         ,(select max(field) from T2) union
        select 'T3'         ,(select max(field) from T3)
    ) AA
);

Here is your sample data

drop database if exists abidibo;
create database abidibo;
use abidibo
create table T1
(
    id int not null auto_increment,
    field int not null,
    primary key (id),
    key (field)
) ENGINE=MyISAM;
create table T2 LIKE T1;
create table T3 LIKE T1;
insert into T1 (field) values (6),(8),(23);
insert into T2 (field) values (8),(45),(23);
insert into T3 (field) values (68),(5),(67);
select * from T1;
select * from T2;
select * from T3;

I loaded your sample data

mysql> drop database if exists abidibo;
Query OK, 3 rows affected (0.00 sec)

mysql> create database abidibo;
Query OK, 1 row affected (0.02 sec)

mysql> use abidibo
Database changed
mysql> create table T1
    -> (
    ->     id int not null auto_increment,
    ->     field int not null,
    ->     primary key (id),
    ->     key (field)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> create table T2 LIKE T1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table T3 LIKE T1;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into T1 (field) values (6),(8),(23);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into T2 (field) values (8),(45),(23);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into T3 (field) values (68),(5),(67);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from T1;
+----+-------+
| id | field |
+----+-------+
|  1 |     6 |
|  2 |     8 |
|  3 |    23 |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from T2;
+----+-------+
| id | field |
+----+-------+
|  1 |     8 |
|  2 |    45 |
|  3 |    23 |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from T3;
+----+-------+
| id | field |
+----+-------+
|  1 |    68 |
|  2 |     5 |
|  3 |    67 |
+----+-------+
3 rows in set (0.00 sec)

mysql>

and here is the result of my query

mysql> select * from
    -> (
    ->     select 'T1' srctable,(select max(field) from T1) maxfield union
    ->     select 'T2'         ,(select max(field) from T2) union
    ->     select 'T3'         ,(select max(field) from T3)
    -> ) A WHERE maxfield =
    -> (select max(maxfield) from
    -> (
    ->     select 'T1' srctable,(select max(field) from T1) maxfield union
    ->     select 'T2'         ,(select max(field) from T2) union
    ->     select 'T3'         ,(select max(field) from T3)
    -> ) AA);
+----------+----------+
| srctable | maxfield |
+----------+----------+
| T3       |       68 |
+----------+----------+
1 row in set (0.00 sec)

mysql>

UPDATE 2012-03-23 11:02 EDT

I just realized something. What if more than one table has the same maximum value?

There is one of three functions you can use to rectify which table you want to see:

• MIN (First Matching Table)
• MAX (Last Matching Table)
• GROUP_CONCAT (All Matching Tables)

I will reload your sample data plus put 68 in T1

    mysql> drop database if exists abidibo;
    Query OK, 3 rows affected (0.01 sec)

    mysql> create database abidibo;
    Query OK, 1 row affected (0.00 sec)

    mysql> use abidibo
    Database changed
    mysql> create table T1
        -> (
        ->     id int not null auto_increment,
        ->     field int not null,
        ->     primary key (id),
        ->     key (field)
        -> ) ENGINE=MyISAM;
    Query OK, 0 rows affected (0.08 sec)

    mysql> create table T2 LIKE T1;
    Query OK, 0 rows affected (0.05 sec)

    mysql> create table T3 LIKE T1;
    Query OK, 0 rows affected (0.08 sec)

    mysql> insert into T1 (field) values (6),(8),(23),(68);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0

    mysql> insert into T2 (field) values (8),(45),(23);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    mysql> insert into T3 (field) values (68),(5),(67);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    mysql> select * from T1;
    +----+-------+
    | id | field |
    +----+-------+
    |  1 |     6 |
    |  2 |     8 |
    |  3 |    23 |
    |  4 |    68 |
    +----+-------+
    4 rows in set (0.00 sec)

    mysql> select * from T2;
    +----+-------+
    | id | field |
    +----+-------+
    |  1 |     8 |
    |  2 |    45 |
    |  3 |    23 |
    +----+-------+
    3 rows in set (0.00 sec)

    mysql> select * from T3;
    +----+-------+
    | id | field |
    +----+-------+
    |  1 |    68 |
    |  2 |     5 |
    |  3 |    67 |
    +----+-------+
    3 rows in set (0.00 sec)

    mysql>

Now let's run each query with MIN, MAX, and GROUP_CONCAT

mysql> select min(srctable) srctables,maxfield from
    -> (
    ->     select 'T1' srctable,(select max(field) from T1) maxfield union
    ->     select 'T2'         ,(select max(field) from T2) union
    ->     select 'T3'         ,(select max(field) from T3)
    -> ) A WHERE maxfield =
    -> (
    ->     select max(maxfield) from
    ->     (
    ->         select 'T1' srctable,(select max(field) from T1) maxfield union
    ->         select 'T2'         ,(select max(field) from T2) union
    ->         select 'T3'         ,(select max(field) from T3)
    ->     ) AA
    -> );
+-----------+----------+
| srctables | maxfield |
+-----------+----------+
| T1        |       68 |
+-----------+----------+
1 row in set (0.03 sec)

mysql> select max(srctable) srctables,maxfield from
    -> (
    ->     select 'T1' srctable,(select max(field) from T1) maxfield union
    ->     select 'T2'         ,(select max(field) from T2) union
    ->     select 'T3'         ,(select max(field) from T3)
    -> ) A WHERE maxfield =
    -> (
    ->     select max(maxfield) from
    ->     (
    ->         select 'T1' srctable,(select max(field) from T1) maxfield union
    ->         select 'T2'         ,(select max(field) from T2) union
    ->         select 'T3'         ,(select max(field) from T3)
    ->     ) AA
    -> );
+-----------+----------+
| srctables | maxfield |
+-----------+----------+
| T3        |       68 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select group_concat(srctable) srctables,maxfield from
    -> (
    ->     select 'T1' srctable,(select max(field) from T1) maxfield union
    ->     select 'T2'         ,(select max(field) from T2) union
    ->     select 'T3'         ,(select max(field) from T3)
    -> ) A WHERE maxfield =
    -> (
    ->     select max(maxfield) from
    ->     (
    ->         select 'T1' srctable,(select max(field) from T1) maxfield union
    ->         select 'T2'         ,(select max(field) from T2) union
    ->         select 'T3'         ,(select max(field) from T3)
    ->     ) AA
    -> );
+-----------+----------+
| srctables | maxfield |
+-----------+----------+
| T1,T3     |       68 |
+-----------+----------+
1 row in set (0.02 sec)

mysql>

Now you have three solutions to choose from.

Give it a Try !!!

Answer 2

Maybe there's a catch I'm missing but the following seems to me a possible solution:

SELECT 'T1' AS Source, field FROM T1
UNION ALL
SELECT 'T2' AS Source, field FROM T2
UNION ALL
SELECT 'T3' AS Source, field FROM T3

ORDER BY field DESC
LIMIT 1

Answer 3

you can try following query ..

with tab1 as
(select 't1' table_name, max(field) from t1
union all 
select 't2' table_name, max(field) from t2
union all 
select 't3' table_name, max(field) from t3
union all 
select 't4' table_name, max(field) from t4
.
.
.
.
.
) 
select * from 
tab1 tab2
where tab2.field = (select max(tab3.field) from tab1 as tab3);

I hope this query will solve your question.