CODEWITHSUNDEEP
mysql
pc_fuel
pc_fuel

Reputation: 786

MySQL- Find the most recent data from 5 tables

I have 5 tables, which have data as follows

Table 1

id_1     name          created_at                        flag
=============================================================
1        Daniel        2021-03-16 12:25:18                0
2        Pearson       2021-03-18 10:25:10                0
3        Scottie        2021-03-19 12:25:18               0
4        Mike           2021-03-21  13:40:03              0

Table 2

id_2     name            created_at                    flag
==============================================================
1        Louis           2021-03-11  10:25:10            0
2        Jessica         2021-03-12  11:36:16             0
3        Maggie         2021-03-21   12:25:18             0
4        Megan            2021-03-13 13:40:03             0

Table 3

id_3     name            created_at                      flag
==================================================================
1        Jon               2021-03-10 12:16:45             0
2       Natalie            2021-03-08 12:45:15             0
3       Victoria           2021-03-08 12:51:00             0
4        Michael           2021-02-12 12:51:17             0

Table 4

id_4      name            created_at                      flag
==============================================================
1        Emily           2021-03-11 13:14:23                0
2        Rose             2021-03-11 12:14:20               0
3       Abigaile           2021-03-11 15:28:56              0
4       Mac               2021-03-11 13:14:23               0

Table 5

id_5      name            created_at                        flag
==================================================================
1        Abella            2021-03-12 12:51:17               0
2        Patrick            2021-03-12 11:48:17              0
3        Josh               2021-03-12 10:52:17              0
4        Goldwin            2021-03-12 13:24:17              0

I want to get the most recent data, and the most outdated data.

This question answers mostly the same, based on two tables. But what if there are 5 (as is my case), or even more. Should I use join or is there anything better?

P.S- Also ,there may be tens of thousands of data from each table to search for.

Upvotes: 1

Views: 47

Answers (2)

Tung Nguyen
Tung Nguyen

Reputation: 845

Is this what you want for the most recent data? For the most outdated, you just change ORDER BY from DESC to ASC.

SELECT t.name, t.created_at
FROM   
   (
       (SELECT name, created_at FROM table1 ORDER BY created_at DESC LIMIT 5)
       UNION
       (SELECT name, created_at FROM table2 ORDER BY created_at DESC LIMIT 5)
       UNION
       (SELECT name, created_at FROM table3 ORDER BY created_at DESC LIMIT 5)
       UNION
       (SELECT name, created_at FROM table4 ORDER BY created_at DESC LIMIT 5)
       UNION
       (SELECT name, created_at FROM table5 ORDER BY created_at DESC LIMIT 5)
   ) AS t
ORDER  BY t.created_at DESC
LIMIT  5;

Upvotes: 1

hollowpurple
hollowpurple

Reputation: 31

In this case, it would be best to perform a Union between the identical tables as a subquery, then perform ordering operations and potentially limits on the result. You can flip the ORDER BY (DESC/ASC) for the two different queries.

SELECT name,
       created_at
FROM   (SELECT name,
               created_at
        FROM   table1
        UNION
        SELECT name,
               created_at
        FROM   table2) t
ORDER  BY created_at DESC
LIMIT  5;

Upvotes: 1

Related Questions