Anton Alstes - 9 months ago 60

SQL Question

If I do a

`dense_rank() over (order by colname),`

I get the same rank for all rows with the same value in the column colname.

However, I want to limit the number of rows with the same rank to @maxrows so that when @maxrows rows have the same value in colname, a new rank is assigned to the next row even if the value of colname is still the same.

How can I achieve this?

Answer Source

You can achieve this via using several ranking functions. We use `ROW_NUMBER()`

in the middle and another column to perform tie-breaking:

```
declare @maxRows int
set @maxRows = 5
; With InitialRanks as (
select DENSE_RANK() OVER (ORDER BY type) as rnk,* from sys.objects
), OrderedRanks as (
select (ROW_NUMBER() OVER (PARTITION BY rnk ORDER by object_id)-1)
/ @maxRows as rn,*
from InitialRanks
)
select DENSE_RANK() OVER (ORDER BY rnk,rn),* from OrderedRanks
```

Here I get only up to 5 columns with each (final) rank value. The ranking is based on `type`

but we use `object_id`

as a secondary column to work out the order in which rows are allowed a particular rank.

Turns out I'd over-complicated the above - there's no need for the first CTE and the first `DENSE_RANK`

since that's effectively acting as a proxy for the `type`

column in the `ROW_NUMBER()`

function - so why not just use the `type`

column directly and simplify things:

```
declare @maxRows int
set @maxRows = 5
; With OrderedRanks as (
select (ROW_NUMBER() OVER (PARTITION BY type ORDER by object_id)-1)
/ @maxRows as rn,*
from sys.objects
)
select DENSE_RANK() OVER (ORDER BY type,rn),* from OrderedRanks
```