Aggregating rows in SQL with missing Booleans

I have the below SQL script which returns the following data from a PostgreSQL DB view table.

SELECT     "V_data".macaddr,    "V_data".sensorid,    "V_data".ts,    "V_data".velocity,    "V_data".temp,    "V_data".highspeed,    "V_data".hightemp,    "V_data".distance,    FROM     sensordb."V_data"    WHERE     "V_data".macaddr like '%abcdef'    AND    (    ("V_data".sensorid = 'abc1') or ("V_data".sensorid = 'a2bc') or ("V_data".sensorid = 'ab3c')     )    AND    "V_data".ts >= 1616370867000    ORDER BY    "V_data".ts DESC;  

Output

macaddr	sensorid	ts	velocity	temp	highspeed	hightemp	distance
abcdef	abc1	1616370867010	25	32			52
abcdef	a2bc	1616370867008	27	35		T	51
abcdef	ab3c	1616370867006	26	30			50
abcdef	abc1	1616370867005	24	36		T	50
abcdef	a2bc	1616370867004	27	31			50
abcdef	abc1	1616370867002	21	30	T		48
abcdef	ab3c	1616370867000	22	33	F		46

I want to aggregate the rows such that I have the latest readings per sensorid for ts, velocity, temp, distance. For the Booleans highspeed and hightemp, I want the latest available Boolean value or an empty cell if no Boolean value was available.

Expected output

macaddr	sensorid	ts	velocity	temp	highspeed	hightemp	distance
abcdef	abc1	1616370867010	25	32	T	T	52
abcdef	a2bc	1616370867008	27	35		T	51
abcdef	ab3c	1616370867006	26	30	F		50

How could I simplify this task?

Thanks.

https://stackoverflow.com/questions/66739160/aggregating-rows-in-sql-with-missing-booleans March 22, 2021 at 09:05AM