CODEWITHSUNDEEP
sqlperformancepostgresqlselectoptimization
ramya
ramya

Reputation: 275

Querying max of timestamp from PostgreSQL DB

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
**

Upvotes: 1

Views: 1775

Answers (2)

Mureinik
Mureinik

Reputation: 311228

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

Upvotes: 1

mlinth
mlinth

Reputation: 3118

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.

Upvotes: 3

Related Questions