Question 113954 in PostgreSQL Question posted in
The official documentation can be found here.

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)

Tags:

2

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search