Efficient latest record query with Postgresql

Question

I need to do a big query, but I only want the latest records.

For a single entry I would probably do something like

SELECT * FROM table WHERE id = ? ORDER BY date DESC LIMIT 1;

But I need to pull the latest records for a large (thousands of entries) number of records, but only the latest entry.

Here's what I have. It's not very efficient. I was wondering if there's a better way.

SELECT * FROM table a WHERE ID IN $LIST AND date = (SELECT max(date) FROM table b WHERE b.id = a.id);

user_1772828 · Answer

You can use a NOT EXISTS subquery to answer this also. Essentially you're saying "SELECT record... WHERE NOT EXISTS(SELECT newer record)":

SELECT t.id FROM table t
WHERE NOT EXISTS
    (SELECT * FROM table n WHERE t.id = n.id AND n.date > t.date)