Mysql – Get top 10 records from two identical tables

MySQL

Suppose I have two identical tables as bellow with different values in it as bellow. I want to get top 4 records ordered by date.
These 4 records should be union of both tables

Sample tables:

ID  userID  Date
1   1111    01/01/2018
2   1112    03/01/2018
3   1113    04/01/2018
4   1114    05/01/2018
5   1115    06/01/2018
6   1116    07/01/2018

ID  userID  Date
1   1117    02/01/2018
2   1118    03/01/2018
3   1119    05/01/2018
4   1120    06/01/2018
5   1121    07/01/2018
6   1122    08/01/2018

Expected output:

ID  userID  Date
1   1111    01/01/2018
1   1117    02/01/2018
2   1112    03/01/2018
3   1113    04/01/2018

How can I get this output. Any help will be greatly appreciated.

Best Answer

Assuming you don't care which result you get in ties, you can use ORDER BY / LIMIT twice, once in each subquery and then a third time in the main query.

This will be more efficient than a normal UNION query from two tables and then LIMIt, as it can use indexes on (Date) on both tables:

    ( SELECT ID, UserID, Date 
      FROM table1 
      ORDER BY Date 
      LIMIT 4
    ) 
UNION ALL
    ( SELECT ID, UserID, Date 
      FROM table2 
      ORDER BY Date 
      LIMIT 4
    ) 
ORDER BY Date 
LIMIT 4 ;
Related Question