Sample Header Ad - 728x90

Select row with max value for multiple columns

3 votes
2 answers
138 views
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