Top N Records in group via RANK, DENSE_RANK or ROW_NUMBER

Many users want to select only TOP n rows per group. This is not possible with the traditional GROUP BY.

You have seen the following example at the beginning of the Ranking functions section. It doesn't show top N records for each partition:

@result =
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Vertical ORDER BY Latency) AS RowNumber,
        RANK() OVER (PARTITION BY Vertical ORDER BY Latency) AS Rank,
        DENSE_RANK() OVER (PARTITION BY Vertical ORDER BY Latency) AS DenseRank
    FROM @querylog;

The results:

TOP N with DENSE RANK

The following example returns the top 3 records from each group with no gaps in the sequential rank numbering of rows in each windowing partition.

@result =
    SELECT
        *,
        DENSE_RANK() 
            OVER (PARTITION BY Vertical ORDER BY Latency) AS DenseRank
    FROM @querylog;

@result =
    SELECT *
    FROM @result
    WHERE DenseRank <= 3;

The results:

TOP N with RANK

@result =
    SELECT
        *,
        RANK() 
            OVER (PARTITION BY Vertical ORDER BY Latency) AS Rank
    FROM @querylog;

    @result =
        SELECT *
        FROM @result
        WHERE Rank <= 3;

The results:

TOP N with ROW_NUMBER

@result =
    SELECT
        *,
        ROW_NUMBER() 
            OVER (PARTITION BY Vertical ORDER BY Latency) AS RowNumber
    FROM @querylog;

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

The results: