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

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)?

CodePudding user response：

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;

CodePudding user response：

You can use PostgreSQL specific DISTINCT ON for this:

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