Running Count of distinct values

Transact-SQL
1

I have a table where I need to assign to each row an order number that reflects the n-th occurance of a distinct value. Too complicated to understand from that explaination. Here is the example:

The first 2 columns belong to the table, the third column is what the expression I am looking for should return.

mailTo            SomeColumn      This is what I need
-----------------------------     ---------------
mailA@examp.le   whatsoever            1
mailB@examp.le   blabla                2
mailB@examp.le   xyxyxyx               2
mailB@examp.le   sometext              2
mailC@examp.le   moretext              3
mailD@examp.le   irrelevant            4
mailD@examp.le   dontread              4

IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test

CREATE TABLE #test (mailTo NVARCHAR(20), SomeColumn nvarchar(10))
insert into #test (mailTo, SomeColumn) values ('mailA@examp.le','whatsoever')
insert into #test (mailTo, SomeColumn) values ('mailB@examp.le','blabla')
insert into #test (mailTo, SomeColumn) values ('mailB@examp.le','xyxyxyx')
insert into #test (mailTo, SomeColumn) values ('mailB@examp.le','sometext')
insert into #test (mailTo, SomeColumn) values ('mailC@examp.le','moretext')
insert into #test (mailTo, SomeColumn) values ('mailD@examp.le','irrelevant')
insert into #test (mailTo, SomeColumn) values ('mailD@examp.le','dontread')

I'm working on SQL Server 14

2 
 Select *
      , Ranking = dense_rank() over(Order By t.mailTo)
   From #test t;

Is this what you are looking for?

2 Likes
3

Yessssss! Thank you very much!!!