Bat_Programmer Bat_Programmer - 4 months ago 29
SQL Question

T-SQL: Row_number() group by

I am using SQL Server 2008 R2 and have a structure as below:

create table #temp( deptid int, regionid int)

insert into #temp
select 15000, 50
union
select 15100, 51
union
select 15200, 50
union
select 15300, 52
union
select 15400, 50
union
select 15500, 51
union
select 15600, 52

select deptid, regionid, RANK() OVER(PARTITION BY regionid ORDER BY deptid) AS 'RANK',
ROW_NUMBER() OVER(PARTITION BY regionid ORDER BY deptid) AS 'ROW_NUMBER',
DENSE_RANK() OVER(PARTITION BY regionid ORDER BY deptid) AS 'DENSE_RANK'
from #temp

drop table #temp


And output currently is as below:

deptid regionid RANK ROW_NUMBER DENSE_RANK
--------------------------------------------------
15000 50 1 1 1
15200 50 2 2 2
15400 50 3 3 3
15100 51 1 1 1
15500 51 2 2 2
15300 52 1 1 1
15600 52 2 2 2


My requirement however is to
row_number
over
regionid
column but by grouping and not row by row. To explain better, below is my desired result set.

deptid regionid RN
-----------------------
15000 50 1
15200 50 1
15400 50 1
15100 51 2
15500 51 2
15300 52 3
15600 52 3


Please let me know if my question is unclear. Thanks.

ZLK ZLK
Answer

Use dense_rank() over (order by regionid) to get the expected result.

select deptid, regionid, 
 DENSE_RANK() OVER( ORDER BY regionid) AS 'DENSE_RANK'
from #temp

Partitioning within a rank/row_number window function will assign numbers within the partitions, so you don't need to use a partition on regionid to order the regionids themselves.

Comments