Maybe it's what you meant be "subsequent query", but you don't actually need to do it in a separate query; you can do the partition & rank in a CTE and select f

Maybe it's what you meant be "subsequent query", but you don't actually need to do it in a separate query; you can do the partition & rank in a CTE and select from the resulting table, all in a single query. It's quite tidy actually.