Select row with max value for multiple columns

Question

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?

score 2 · Answer 1 · answered May 13 '25 at 17:05

Probably not more acceptable, just a different version of the same.

with singleRow as (select distinct
  first_value(result_id) over (order by t10 desc) as result_id_10,
  first_value(t10) over (order by t10 desc) as value_10,
  't10' as tag_10,
first_value(result_id) over (order by t30 desc) as result_id_30,
  first_value(t30) over (order by t30 desc) as value_30,
  't30' as tag_30,
first_value(result_id) over (order by t60 desc) as result_id_60,
  first_value(t60) over (order by t60 desc) as value_60,
  't60' as tag_60,
first_value(result_id) over (order by t120 desc) as result_id_120,
  first_value(t120) over (order by t120 desc) as value_120,
  't120' as tag_120
from speeds
)
select result_id_10 as result_id, value_10 as value, tag_10 as tag from singleRow
union all
select result_id_30, value_30, tag_30 from singleRow
union all
select result_id_60, value_60, tag_60 from singleRow
union all
select result_id_120, value_120, tag_120 from singleRow

https://www.db-fiddle.com/f/j8BrtEyXLQStw35xSkmPHq/0

But you could only use the first select alone if you are ok with getting the results as a single row - if you process the data after the query anyway:

result_id_10	value_10	result_id_30	value_30	result_id_60	value_60	result_id_120	value_120
36cdec57-5d17-4260-a25f-c772a2c942fb	11.339522	36cdec57-5d17-4260-a25f-c772a2c942fb	11.339522	9fb35758-c9f0-4205-b1c1-3e0c8671a996	11.134026	a7227251-82c8-4956-be44-fa54f96cab6d	11.278638

Erwin Brandstetter · Answer 2 · 2025-05-14T12:13:55.747

The general approach won't get much faster, but you can improve details:

(SELECT result_id, t10 AS value, 't10' AS tag FROM speeds ORDER BY t10 DESC NULLS LAST LIMIT 1)
UNION All
(SELECT result_id, t30 , 't30'  FROM speeds ORDER BY t30  DESC NULLS LAST LIMIT 1)
UNION All
(SELECT result_id, t60 , 't60'  FROM speeds ORDER BY t60  DESC NULLS LAST LIMIT 1)
UNION All
(SELECT result_id, t120, 't120' FROM speeds ORDER BY t120 DESC NULLS LAST LIMIT 1)

UNION ALL instead of UNION. More correct - you do not want to eliminate duplicate result rows (even if no rows are removed in this case). Slightly faster as it does not try. And original order of result rows is preserved. See:
- Are results from UNION ALL clauses always appended in order?
DESC NULLS LAST makes the added WHERE obsolete. Corner-case difference: a row with a null value is returned when there are no other - while your original returns no row for that. If that can happen, define desired behavior ... See:
- Sort NULL values to the end of a table
No need for subqueries. Just enclose each SELECT in parentheses to apply ORDER BY and LIMIT locally.
- Get rows for a given time range, plus one preceding row
Spelling out column aliases once is enough. Postgres takes output column names from the first UNION leg and ignores the rest.

If the table is big, a separate index for each of the value columns makes this query fast. Possibly a covering index. As always, weigh overhead vs. benefit. See:

Do covering indexes in PostgreSQL help JOIN columns?

The bare minimum goes a long way:

CREATE INDEX speed_t10_idx ON speed (t10);
CREATE INDEX speed_t30_idx ON speed (t30);
...

The optimum:

CREATE INDEX speed_t10_idx ON speed (t10 DESC NULLS LAST) INCLUDE (result_id);
...

If there can be more than one row tieing for the greatest value in each value column, add more ORDER BY expressions to the query (and index) to make the result deterministic, or accept the arbitrary pick you get from this query and your original alike.

Select row with max value for multiple columns

2 Answers2