Output data without duplicates by maximum date

October 18, 2023

I have two tables: Test1 and Test2.

Test1:

Cat	Dog	Date
123412	1111111	2023-01-01 12:01:01
123412	2222222	2023-01-01 13:02:01

Test2

Cat	Parrot	Date
123412	1412111	2023-01-01 14:01:01
123412	2242222	2023-01-01 15:02:01

The script for joining these tables looks like this:

select 

test1.cat
test2.cat

from Test1 test1

left join Test2 test2 on test2.cat=test1.cat

It is necessary to make sure that only 1 cat is displayed for the maximum date (without duplicates). Using a subquery or conditions inside a JOIN or WHERE.

I tried to do this subquery:

select 

test1.cat
test2.cat

from Test1 test1

left join Test2 test2 on test2.cat=test1.cat 

and test2.date = (select max(test2.date) from Test2) 

where test1.date = (select max(test1.date) from Test1)

but nothing happened, it displays emptiness

I apologize if my explanations are unclear

>Solution :

Have you tried something like this?

SELECT Cat,MAX(date) as maxDate
FROM (
   SELECT test1.cat, test1.date
   FROM   Test1 test1
   UNION
   SELECT test2.cat, test2.date
   FROM   Test1 test2
)
GROUP BY Cat.