Union of 4 tables with same column types (but different data) order by max time in AWS Athena?

Question

I'm having an issue I have 4 tables that look like this:

device	volume1	volume2	time
device_id	x	y	time_devicemessage

Each table is for one single device so I have 4 devices, which send messages in different timestamps.

I would like to know a query for how to unite these 4 tables into 1 but showing only the last volume data from each table based on its timestamp.

So it may look like this:

device	volume1	volume2	time
deviceA	lastvalue (x)	lastvalue (y)	time_devicemessageA
deviceB	lastvalue (x)	lastvalue (y)	time_devicemessageB
deviceC	lastvalue (x)	lastvalue (y)	time_devicemessageC
deviceD	lastvalue (x)	lastvalue (y)	time_devicemessageD

Thank you very much for your support, I would appreciate lots the help!

Regards, Ruben.

Answer 1

You could get the last row of each table and then UNION these four rows, something like this:

SELECT device, volume1, volume2, time FROM device1 ORDER BY time DESC LIMIT 1

UNION 

SELECT device, volume1, volume2, time FROM device2 ORDER BY time DESC LIMIT 1

UNION 

SELECT device, volume1, volume2, time FROM device3 ORDER BY time DESC LIMIT 1

UNION 

SELECT device, volume1, volume2, time FROM device4 ORDER BY time DESC LIMIT 1;