SQL: Pinned rows and row number calculation

Question

We have a requirement to assign row number to all rows using following rule

• Row if pinned should have same row number
• Otherwise sort it by GMD

Example:

 ID  GMD   IsPinned 

 1   2.5    0        
 2    0     1        
 3    2     0        
 4    4     1        
 5    3     0       

Should Output

ID  GMD   IsPinned  RowNo

5    3    0          1   
2    0    1          2  
1    2.5  0          3  
4    4    1          4  
3    2    0          5  

Please Note row number for Id's 2 and 4 stayed intact as they are pinned with values of 2 and 4 respectively even though the GMD are not in any order Rest of rows Id's 1, 3 and 5 row numbers are sorted using GMD desc

I tried using RowNumber SQL 2012 however, it is pushing pinned items from their position

Answer 1

Here's a set-based approach to solving this. Note that the first CTE is unnecessary if you already have a Numbers table in your database:

declare @t table (ID int,GMD decimal(5,2),IsPinned bit)
insert into @t (ID,GMD,IsPinned) values
(1,2.5,0), (2, 0 ,1), (3, 2 ,0), (4, 4 ,1), (5, 3 ,0)

;With Numbers as (
    select ROW_NUMBER() OVER (ORDER BY ID) n from @t
), NumbersWithout as (
    select
        n,
        ROW_NUMBER() OVER (ORDER BY n) as rn
    from
        Numbers
    where n not in (select ID from @t where IsPinned=1)
), DataWithout as (
    select
        *,
        ROW_NUMBER() OVER (ORDER BY GMD desc) as rn
    from
        @t
    where
        IsPinned = 0
)
select
    t.*,
    COALESCE(nw.n,t.ID) as RowNo
from
    @t t
        left join
    DataWithout dw
        inner join
    NumbersWithout nw
        on
            dw.rn = nw.rn
        on
            dw.ID = t.ID
order by COALESCE(nw.n,t.ID)

Hopefully my naming makes it clear what we're doing. I'm a bit cheeky in the final SELECT by using a COALESCE to get the final RowNo when you might have expected a CASE expression. But it works because the contents of the DataWithout CTE is defined to only exist for unpinned items which makes the final LEFT JOIN fail.

Results:

ID          GMD                                     IsPinned RowNo
----------- --------------------------------------- -------- --------------------
5           3.00                                    0        1
2           0.00                                    1        2
1           2.50                                    0        3
4           4.00                                    1        4
3           2.00                                    0        5

---

Second variant that may perform better (but never assume, always test):

declare @t table (ID int,GMD decimal(5,2),IsPinned bit)
insert into @t (ID,GMD,IsPinned) values
(1,2.5,0), (2, 0 ,1), (3, 2 ,0), (4, 4 ,1), (5, 3 ,0)

;With Numbers as (
    select ROW_NUMBER() OVER (ORDER BY ID) n from @t
), NumbersWithout as (
    select
        n,
        ROW_NUMBER() OVER (ORDER BY n) as rn
    from
        Numbers
    where n not in (select ID from @t where IsPinned=1)
), DataPartitioned as (
    select
        *,
        ROW_NUMBER() OVER (PARTITION BY IsPinned ORDER BY GMD desc) as rn
    from
        @t
)
select
    dp.ID,dp.GMD,dp.IsPinned,
    CASE WHEN IsPinned = 1 THEN ID ELSE nw.n END as RowNo
from
    DataPartitioned dp
        left join
    NumbersWithout nw
        on
            dp.rn = nw.rn
order by RowNo

In the third CTE, by introducing the PARTITION BY and removing the WHERE clause we ensure we have all rows of data so we don't need to re-join to the original table in the final result in this variant.