CODEWITHSUNDEEP
mysqlsqlmariadbgroupwise-maximum
karan sharma
karan sharma

Reputation: 33

Get maximum date and corresponding row out of three tables

MariaDB Version: 10.1.40-MariaDB

I have a 3 table Like A, B, C all three table have a column "update_date" and "update_emp_id". Now, I am trying to get max update_date with update_emp_id among all three Tables

for example Table "A"

+----+------+------+------+-------+
| id | update_date |update_emp_id |
+----+------+------+------+-------+
|  1 | 2019-09-05  |  5117        |
|  2 | 2019-09-07  |  5118        |
|  3 | 2019-09-09  |  5117        |
|  4 | 2019-09-11  |  5118        |
|  5 | 2019-09-10  |  5119        |
+----+------+------+------+-------+

Table "B"

+----+------+------+------+-------+
| id | update_date |update_emp_id |
+----+------+------+------+-------+
|  1 | 2019-09-08  |  5117        |
|  2 | 2019-09-07  |  5118        |
|  3 | 2019-09-10  |  5117        |
|  4 | 2019-09-15  |  5118        |
|  5 | 2019-09-10  |  5119        |
+----+------+------+------+-------+

Table "C" 

+----+------+------+------+-------+
| id | update_date |update_emp_id  |
+----+------+------+------+-------+
|  1 | 2019-09-06  |  5117        |
|  2 | 2019-09-16  |  5118        |
|  3 | 2019-09-09  |  5117        |
|  4 | 2019-09-12  |  5118        |
|  5 | 2019-09-10  |  5119        |
+----+------+------+------+-------+

Now I want To get max update_date with update_emp_id among all three Tables

Like update_date=2019-09-16 and update_emp_id=5118

Upvotes: 0

Views: 50

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

You can get the row corresponding to maximum update_date value in each table using ORDER BY update_date DESC LIMIT 1. Now, you can UNION results from all the table(s), and then again use ORDER BY to get the latest row out of all:

( SELECT update_emp_id, update_date FROM tableA 
  ORDER BY update_date DESC LIMIT 1 )

UNION

( SELECT update_emp_id, update_date FROM tableB 
  ORDER BY update_date DESC LIMIT 1 )

UNION

( SELECT update_emp_id, update_date FROM tableC 
  ORDER BY update_date DESC LIMIT 1 )

ORDER BY update_date DESC LIMIT 1

Result

| update_emp_id | update_date |
| ------------- | ----------- |
| 5118          | 2019-09-16  |

View on DB Fiddle

Upvotes: 1

Related Questions