36 ms·
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 ro
by sa46 4y ago
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
- 1500100900 4y ago- "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)