1

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.

  • 1
    How do you filter rows? – McNets Mar 21 '18 at 13:46
8

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.