3 ms·
I believe the demo query SELECT DISTINCT ON (location) location, time, report FROM weather_reports ORDER BY location, time DESC; could also be wri
by claytonjy 7y ago
I believe the demo query
SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
could also be written with a self-join like so
SELECT location, time, report
FROM weather_reports
JOIN (
SELECT location, max(time) AS time
FROM weather_reports
GROUP BY location
) as _ USING (location, time)
ORDER BY location;
though I'd be curious what the query-plan differences are. I suspect the DISTINCT ON might translate more closely to a lateral join
SELECT
FROM (
SELECT DISTINCT(location)
FROM weather_reports
) AS locations
JOIN LATERAL (
SELECT location, time, report
FROM weather_reports
WHERE location = locations.location
ORDER BY ts DESC
LIMIT 1
) AS reports ON TRUE
ORDER BY location;
But I really can't say.
- paulddraper 7y agoThe first only works if location,time is unique. In the example it may be, not necessarily in general. And yes, the JOIN LATERAL would be a good substitute. P.S. More concise is CROSS JOIN ... rather than JOIN ... ON TRUE
- zeroimpl 7y agoIn general if you query a table twice, the query planner will visit that table twice. So DISTINCT ON is almost always more efficient. The main exception I’ve seen is when using it in a view, if you try to filter the results of the view, it usually can’t optimize a DISTINCT ON, but sometimes can for GROUP BY (though that might have improved on newer versions)