How to set partition id/name for row partitions in SQL Server?

Question

How to set partition id/name for row partitions in SQL Server?

name surname val
a  b      10
c  d      2
a  b      11
a  b      13

result (partitioned by name and surname):

 name surname val rowno partitionid

 a    b        10   1    1
 a    b        11   2    1
 a    b        13   3    1
 c    d        2    1    2

Answer 1

DECLARE @table TABLE( name CHAR(1) , surname CHAR(1) , val TINYINT )

INSERT INTO @table
VALUES ( 'a' , 'b' , 10 ) 
, ( 'c' , 'd' , 2 )
, ('a' , 'b' , 11 )
, ( 'a' , 'b' , 13 )

SELECT * FROM @table

SELECT *
, ROW_NUMBER() OVER ( PARTITION BY name, surname ORDER BY val ) as rowno
, DENSE_RANK() OVER ( ORDER BY name ) as partitionid
FROM @table

Regards!

Answer 2

The dense_rank window function seems to fit the bill:

SELECT *,
       DENSE_RANK() OVER (PARTITION BY name, surname ORDER BY val) AS rowno,
       DENSE_RANK() OVER (ORDER BY name, surname) AS partitionid
FROM   mytable