Optimize query for multiple unions

Question

We have a table like

------------------------------------------
| user_id | timestamp | key      | value |
------------------------------------------
   99     | 1598603308 | Spo2 |      99
   99     | 1598603318 | Spo2 |      98
   99     | 1598603328 | Spo2 |      96
   99     | 1598603338 | Spo2 |      97
...
...
   99     | 1598603307 | Breath |      99
   99     | 1598603311 | Breath |      98
   99     | 1598603315 | Breath |      96
   99     | 1598603319 | Breath |      97

The idea is to get the latest Breath & latest Spo2 for the user with id 99.

select user_id, timestamp, key, value from session_records
where
user_id = 99 and key = 'Spo2' and value > 0 order by timestamp desc limit 1
**UNION**
select user_id, timestamp, key, value from session_records
where
user_id = 99 and key = 'Breath' and value >= 0 order by timestamp desc limit 1

Now the key can vary. It can be HeartRate or Sdnn or something dynamically passed in.

Is there any other way to write this query without unions?

Answer 1

Just use distinct on:

select distinct on (user_id, key) s.*
from session_record s
where user_id = 99 and key in ('Breath', 'Spo2')
order by user_id, key, timestamp desc

You can adapt the where clause predicates as you wish - the rest of the query remains the same.