4 ms·
Window functions are super handy to turn a correlated subquery into a join, because they make group-wise limits fairly easy (just put a ROW_NUMBER() concerning
by blattimwind 8y ago
Window functions are super handy to turn a correlated subquery into a join, because they make group-wise limits fairly easy (just put a ROW_NUMBER() concerning the relevant partition, i.e. what would be your GROUP BY in the correlated subquery, in your query and select from that query filtering on rank; then join on whatever you previously correlated on).
Basically: SELECT ... FROM (SELECT ..., ROW_NUMBER() OVER (PARTITION BY my_group [ORDER BY...]) AS rank) AS foo WHERE foo.rank <= 10
Without window functions you can usually only do this using a correlated subquery, which usually is much slower for fairly obvious reasons.
- btilly 8y agoThat's another common variation, using a where in the outer query to select only a few of the rows from the window function. And I've also used them as a better performing version of a full outer join - do a select of the combined fields from a window function query that is selected off of a UNION ALL. You have to jump a lot of hoops to do it, but the performance win can be worthwhile.
- da_chicken 8y agoYup, if all you ever learn is one windowing function, the "greatest N per group" pattern with ROW_NUMBER() is easily the most common.
- deleted 8y ago[deleted]