CODEWITHSUNDEEP
sqldatabasepostgresql
Maxim
Maxim

Reputation: 57

Select max and latest value from table in PostgreSQL

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)

Upvotes: 0

Views: 230

Answers (2)

The Impaler
The Impaler

Reputation: 48770

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.

Upvotes: 0

Ivan Yuzafatau
Ivan Yuzafatau

Reputation: 608

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

Upvotes: 1

Related Questions