LIMIT 1 by Column On Query Returning Multiple Results?

I have a user_certifications table, with these props:

int user_id
int cert_id
timestamp last_updated

​x
 
int user_idint cert_idtimestamp last_updated​

So, a user can be listed multiple times with a variety of certs. I have a list of user_ids for which I need to get ONLY the most recently updated record. So for one user it would be:

SELECT user_id, last_updated 
FROM user_certifications
WHERE user_id == x
ORDER BY last_updated DESC
LIMIT 1

 
SELECT user_id, last_updated FROM user_certificationsWHERE user_id == xORDER BY last_updated DESCLIMIT 1​

How do I do this efficiently if I need ONLY the last dated entry for each of a number of users. E.g. similar query, but WHERE user_id IN (x,y,z) and returning one entry per user, with the latest date?

P.S. – I apologize for the title, I don’t know how to word this.

Answer

Use distinct on:

SELECT DISTINCT ON (user_id), uc.* 
FROM user_certifications uc
ORDER BY user_id, last_updated DESC

 
SELECT DISTINCT ON (user_id), uc.* FROM user_certifications ucORDER BY user_id, last_updated DESC​

Advertisement

Answer