3 ms·
Doesn't DISTINCT work in this case? SELECT DISTINCT ON (vt.id) row_to_json(vt.*) FROM vt JOIN vy ON vt.id = vy.tid WHERE vt.id = 1 AND vy.amount > 1;
by e12e 5y ago
Doesn't DISTINCT work in this case?
SELECT DISTINCT ON (vt.id)
row_to_json(vt.*) FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1;
- ThrustVectoring 5y agoThe SQL in question which was problematic for me (tables renamed): SELECT posts.*, MAX(COALESCE(comments.created_at, posts.created_at)) AS latest_timestamp FROM posts LEFT OUTER JOINS comments ON posts.id = comments.post_id GROUP BY posts.id ORDER BY latest_timestamp desc In short, this is sorting posts by the most recent comment, with a fallback to the post date if the post has no comments on it. Hard to get rid of the grouping here and get the same data back.
- e12e 5y agoI see, that makes more sense.
- biggerfisch 5y agoI'm the OP of the DBA StackExchange post, and while yes, it does technically work in this case, you do lose some abilities. For one, its much harder to `count(*)` rows with `DISTINCT`. Also, `DISTINCT` uses a totally different mode in planning that requires first retrieving all the rows and then finally filtering them. This is _much_ slower and generally not very fun to deal with!