Any N Records per group

Just as you sometimes want to get the "TOP n rows per group". Sometimes you just want "ANY n rows per group".

For example, "list any 10 customers per zipcode".

@result =
            OVER (PARTITION BY ZipCode ORDER BY 1) AS RowNumber
    FROM @customers;

@result =
    SELECT *
    FROM @result
    WHERE RowNumber <= 10;

This technique makes use of the "ORDER BY 1" pattern - which effectively means no ordering is performed.

results matching ""

    No results matching ""