CODEWITHSUNDEEP
sqlpostgresqlselecttimestampmax
MathMan 99
MathMan 99

Reputation: 755

Potsgres SQL: select timestamp prior to max timestamp

I have a table in Postgres with timestamps:

timestamp
2022-01-01 00:52:53
2022-01-01 00:57:12
...
2022-02-13 11:00:31
2022-02-13 16:45:10

How can I select the timestamp closest to max timestamp? Meaning, I want the timestamp 2022-02-13 11:00:31.

I am looking for something like max(timestamp)-1 so I can do on a recurring basis. Thank you

Upvotes: 0

Views: 252

Answers (2)

MoDo
MoDo

Reputation: 230

I think the following query might meet your requirements:

SELECT MAX(date_col) FROM test WHERE date_col < (SELECT MAX(date_col) from test);

See DB Fiddle

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

You can do:

select *
from (
  select *,
    rank() over(order by timestamp desc) as rk
  from t
) x
where rk = 2

See running example at DB Fiddle.

Upvotes: 1

Related Questions