Ranking by Groups in SQL SERVER

Question

I need to rank the following groups by 'String' and 'Is_sep' and keep the order by 'Id': (SQL Server)

ID      | String    | Is_Sep    | Rank      | Rank_DESC
1       | XX        | 0         | 1         | 3
2       | XX        | 0         | 2         | 2
3       | XX        | 1         | 3         | 1
4       | XX        | 0         | 1         | 3
5       | XX        | 0         | 2         | 2
6       | XX        | 1         | 3         | 1
7       | YY        | 0         | 1         | 4
8       | YY        | 0         | 2         | 3
9       | YY        | 0         | 3         | 2
10      | YY        | 1         | 4         | 1
11      | ZZ        | 0         | 1         | 2
12      | ZZ        | 1         | 2         | 1
13      | ZZ        | 0         | 1         | 3
14      | ZZ        | 0         | 2         | 2
15      | ZZ        | 1         | 3         | 1

Any idea of how to do it?

Thanks!

What do you mean by “ranking”? Do you mean ordering? Obtaining a row-number after ordering? Or do you mean “relevance” according to some criteria? — Dai, Jul 31 '20 at 23:53

GMB · Accepted Answer · 2020-08-01T12:29:35.197

This is a kind of gaps-and-island problem, where is_sep marks the end of each island.

I would use a window sum to define the groups, and then row_number():

select 
    id,
    string,
    sep,
    row_number() over(partition by string, grp order by id) rn,
    row_number() over(partition by string, grp order by id desc) rn_desc
from (
    select 
        t.*,
        sum(is_sep) over(partition by string order by id desc) grp
    from mytable t
) t
order by id

Note that, for this sample data, you don't really need string in the partition, since is_sep also indicates the transition between string values. So this works equally well:

select 
    id,
    string,
    is_sep,
    row_number() over(partition by grp order by id) rn,
    row_number() over(partition by grp order by id desc) rn_desc
from (
    select 
        t.*,
        sum(is_sep) over(order by id desc) grp
    from mytable t
) t
order by id

Demo on DB Fiddlde:

id | string | is_sep | rn | rn_desc
-: | :----- | -----: | -: | ------:
 1 | XX     |      0 |  1 |       3
 2 | XX     |      0 |  2 |       2
 3 | XX     |      1 |  3 |       1
 4 | XX     |      0 |  1 |       3
 5 | XX     |      0 |  2 |       2
 6 | XX     |      1 |  3 |       1
 7 | YY     |      0 |  1 |       4
 8 | YY     |      0 |  2 |       3
 9 | YY     |      0 |  3 |       2
10 | YY     |      1 |  4 |       1
11 | ZZ     |      0 |  1 |       2
12 | ZZ     |      1 |  2 |       1
13 | ZZ     |      0 |  1 |       3
14 | ZZ     |      0 |  2 |       2
15 | ZZ     |      1 |  3 |       1

`sum(is_sep) over (partition by string order by id desc)` is really much simpler. — Gordon Linoff, Aug 01 '20 at 12:16
@GordonLinoff: ah yes, that’s much simpler indeed - no need for `coalesce()`, nor for `rows between`. Thanks! — GMB, Aug 01 '20 at 12:31

Ranking by Groups in SQL SERVER

1 Answers1