Querying max of timestamp from PostgreSQL DB

Question

Assume there are 3 rows in a PostgreSQL table named tracker. I want to fetch the latest updated record alone. Please help me achieving it.

(issue_id,priority,ingest_date)
(1,1,"2015-01-27 00:00:00")
(1,2,"2015-01-28 00:00:00")
(1,3,"2015-01-29 00:00:00")

I tried giving

select * 
from tracker 
where ingest_date = (select max(ingest_date) from tracker);

This works fine for me. But is there any better way I can query the DB?

Thanks in advance.

I want something like

select * 
from etl_change_fact 
where ingest_date = max(ingest_date);

But I get this error

**ERROR: aggregates not allowed in WHERE clause
**

If you have an index on ingest_date your first query is probably the most efficient way to do it. — user330315
– user330315, Commented Feb 2, 2015 at 14:31
I agree with horse. Assuming an index, the subquery will turn which by most other methods will be an index or table scan into an index seek. Very efficient. — TommCatt
– TommCatt, Commented Feb 3, 2015 at 6:12

mlinth · Accepted Answer · 2015-02-02 13:23:26Z

3

You can do it as follows. Just select the latest record...

 SELECT * from tracker ORDER BY ingest_date DESC LIMIT 1

This query will always return only one record. Your query can return multiple rows if ingest_date contains duplicates.

answered Feb 2, 2015 at 13:23

mlinth

3,1586 gold badges35 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2015-02-02 13:32:27Z

1

If you know the ingest_date is unique (i.e., there is only one row which is the latest), you could use a fetch first clause:

SELECT      *
FROM        tracker
ORDER BY    ingest_date DEXC
FETCH FIRST 1 ROWS ONLY

If this assumption can't be made, you could use the rank window function to return all the "latest" records:

SELECT issue_id, priority, ingest_date
FROM   (SELECT issue_id, priority, ingest_date, 
               RANK() OVER (ORDER BY ingest_date DESC) AS rk
        FROM   tracker)
WHERE  rk = 1

edited Feb 2, 2015 at 13:32

answered Feb 2, 2015 at 13:26

Mureinik

316k54 gold badges400 silver badges405 bronze badges

1 Comment

Ben Grimm Over a year ago

It's really not necessary to create a window for a problem this simple, select * from table where col = (select max(col) from table) will result in a more efficient query plan.

Collectives™ on Stack Overflow

Querying max of timestamp from PostgreSQL DB

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related