I am trying to get a group number for all similar values in SQL Server.

Here's the result set I have:

Values              SortOrder
------------------------------
test note 1         1
test note 1         2
test note 3         3
test 10             4
test 10             5
test 11             6

I am trying to get the following result set:

Values              SortOrder        Group
------------------------------------------
test note 1         1                 1
test note 1         2                 1
test note 3         3                 2
test 10             4                 3
test 10             5                 3
test 11             6                 4

I tried various techniques like Dense_Rank, Rank, NTile, etc. but each time the Group Number is a running number series.

Select
Dense_Rank() Over (Order By SortOrder) Rank,
SortOrder,
Values from table where values is not null

Any help would be greatly appreciated.

CodePudding user response：

Dense Rank could be used the job, but you need to order by the column you need to rank against, just a slight modification:

Select Dense_Rank() Over (Order By [Values]) Rank, SortOrder, [Values]
from table 
where [values] is not null;

Of course this sorts the data as a string, to get the ranks based on the numerical content only, you would need to order by that specifically.

I don't know enough about your data, but based on the sample provided you could assume the rightmost characters following the last space are always numerical values, in which case the following would give your desired ranking:

select Dense_Rank() Over (Order By Try_Convert(int,Right([values], CharIndex(' ', Reverse([values]))))) Rank,
SortOrder, [Values]
from table
order by sortorder;

See an example working fiddle

CodePudding user response：

This looks like a gaps-and-island problem - assuming that the same value might appear in different groups of consecutive rows.

We can solve it with by incremeting a window sum everytime the value changes, like so:

select t.*, 
    sum(case when val = lag_val then 0 else 1 end) over(order by sortOrder) as grp
from (
    select t.*, lag(val) over(order by sortOrder) lag_val
    from mytable t
) t

fiddle