Postgres: get the last lines for a set of keys

advertisements

I have a simple table for the events log:

uid | event_id | event_data
----+----------+------------
  1 |  1       | whatever
  2 |  2       |
  1 |  3       |
  4 |  4       |
  4    5       |

If I need the latest event for a given user, that's obvious:

SELECT * FROM events WHERE uid=needed_uid ORDER BY event_id DESC LIMIT 1

However, suppose I need the latest events for each user id in an array. For example, for the table above and users {1, 4} I'd expect events {3, 5}. Is that possible in plain SQL without resorting to a pgSQL loop?


A Postgres specific solution is to use distinct on which is usually faster than the solution using a window function:

select distinct on (uid) uid, event_id, event_data
from events
where uid in (1,4)
order by uid, event_id DESC