Select max and latest value from table in PostgreSQL-CodePudding

I have a table like this:

value  ts
2.0    1
3.0    5
7.0    3
1.0    2
5.0    4

I need to select max value, min value and value with max ts. Is it possible to do it with one query? Is there an aggregate function which returns the first value from table? If so, I could do something like

select max(value), min(value), first(value) from table order by ts desc;

(For this query max value is 7.0, min value is 1.0, and value with max ts is 3.0)

CodePudding user response：

SELECT
  t2.max_value,
  t2.min_value,
  t1.value
FROM
  table AS t1
  JOIN
  (
    SELECT
      MAX(value) AS max_value,
      MIN(value) AS min_value,
      MAX(ts) AS max_ts
    FROM
      table
  ) AS t2 ON t2.max_ts = t1.ts

CodePudding user response：

You can do:

select min(value), max(value), (select value from t order by ts desc limit 1) from t

Result:

min  max  max
---  ---  ---
1.0  7.0  3.0

See example at DB Fiddle.