I have a table with speeds at different time intervals:
CREATE TABLE speeds (
result_id uuid NULL,
t10 float4 NULL,
t30 float4 NULL,
t60 float4 NULL,
t120 float4 NULL);
And some example values:
INSERT INTO speeds (result_id,t10,t30,t60,t120) VALUES
('9cce0257-122a-43a9-b3d7-4af6ea07361b',10.495212,10.495212,10.495212,10.495212),
('0e42f6d3-363e-464a-a7e0-a923afd55254',9.220896,9.351419,9.193572,9.494948),
('9fb35758-c9f0-4205-b1c1-3e0c8671a996',11.134026,11.134026,11.134026,9.671922),
('36cdec57-5d17-4260-a25f-c772a2c942fb',11.339522,11.339522,10.826232,10.826232),
('a7227251-82c8-4956-be44-fa54f96cab6d',11.300294,10.272741,10.272741,11.278638);
And I have a very ugly query to get the results at the maximum speed over all time intervals:
select * from (select result_id, t10 as value, 't10' as tag from speeds where t10 is not null order by t10 desc limit 1)
union select* from (select result_id, t30 as value, 't30' as tag from speeds where t30 is not null order by t30 desc limit 1)
union select* from (select result_id, t60 as value, 't60' as tag from speeds where t60 is not null order by t60 desc limit 1)
union select* from (select result_id, t120 as value, 't120' as tag from speeds where t120 is not null order by t120 desc limit 1)
And the results are:
36cdec57-5d17-4260-a25f-c772a2c942fb 11.339522 t10
a7227251-82c8-4956-be44-fa54f96cab6d 11.278638 t120
36cdec57-5d17-4260-a25f-c772a2c942fb 11.339522 t30
9fb35758-c9f0-4205-b1c1-3e0c8671a996 11.134026 t60
Is it possible to replace this terrible query with something more acceptable?
Asked by Noah Rivers
(31 rep)
May 13, 2025, 03:13 PM
Last activity: May 14, 2025, 12:13 PM
Last activity: May 14, 2025, 12:13 PM