bill bill - 4 years ago 261
SQL Question

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

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 Source
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!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download