Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

Interesting, I hadn't thought of it that way. Looking through our codebase:

- We get a lot of mileage out of DISTINCT ON to get the most recent version of a row for some subset of columns. I think this a different use case than what you refer to.

- We typically use DISTINCT to get PKs for a table when starting from another table. Like, find all user IDs that commented on a given story by looking at the join table.

    SELECT DISTINCT user_id FROM story_comment WHERE story_id = 4


- "most recent" is usually best done with a max() with an adequate index or, when that's not possible, with a LATERAL JOIN, an ORDER BY and a LIMIT 1

- SELECT id FROM users WHERE EXISTS (SELECT 1 FROM story_comment WHERE story_id = 4 AND story_comment.user_id = users.id)




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: