Select the latest records (i.e. ORDER by) when IDs IN multiple values

Question

For example, a devices table like this

id	user_id	last_used_at
1	111	2000-01-01 00:00:00
2	111	2003-01-01 00:00:00
3	222	2000-01-01 00:00:00
4	222	2003-01-01 00:00:00

• select the last used device of user_id = 111

  SELECT *
  FROM devices
  WHERE user_id = 111
  ORDER BY last_used_at DESC
  LIMIT 1;
  -- which should select device id = 2
  

• select all devices of user_id IN (111, 222)

  SELECT *
  FROM devices
  WHERE user_id IN (111, 222);
  

The question is - how to select the last used devices of user_id IN (111, 222)?

Answer 1

You can use PostgreSQL specific DISTINCT ON for this:

SELECT DISTINCT ON (user_id) *
FROM devices
ORDER BY user_id, last_used_at DESC

Answer 2

There are multiple ways to do this. Using WINDOW function is 1 of them -

SELECT *
  FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY last_used_at DESC) RN
          FROM devices)
 WHERE user_id IN (111, 222)
   AND RN = 1;