I also generally use a partitioned row_number(), ordering on some selection criteria (highest number of x with provider y, etc).

Is the output of Rank defined by ANSI to always support this method in all implementations? My guess is that it's only guaranteed to be in order, but that the density of the default invocation could vary.

Still, this is clever and hadn't occurred to me before, so thanks for expanding my box.