1

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
**
Mureinik
  • 297,002
  • 52
  • 306
  • 350
ramya
  • 275
  • 1
  • 5
  • 13
  • If you have an index on `ingest_date` your first query is probably the most efficient way to do it. –  Feb 02 '15 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 Feb 03 '15 at 06:12

2 Answers2

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.

mlinth
  • 2,968
  • 6
  • 30
  • 30
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
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 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. – Ben Grimm Feb 02 '15 at 13:50