July 31, 2017 at 4:19 pm
Hi All,
I have the following table:
declare @T table
(
ID int identity
, NUM int
);
insert into @T values
(0),(0),(0),(1),(1),(0),(0),(0),(1),(1),(0),(0),(0);
ID NUM
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 0
9 1
10 1
11 0
12 0
13 0
I would like to rank each group in NUM column,like this
ID NUM Rank
1 0 1
2 0 1
3 0 1
4 1 2
5 1 2
6 0 3
7 0 3
8 0 3
9 1 4
10 1 4
11 0 5
12 0 5
13 0 5
I tried to use DENSE_RANK(),RANK() but no luck.Any Ideas?
Thank you advance
July 31, 2017 at 5:11 pm
Here's one way (I have a nagging feeling this can be simplified, but it's getting a bit late in the day so I'm not sure how much more thought I'll give it today):
WITH
numbered AS (SELECT *, rn=ROW_NUMBER() OVER (ORDER BY ID), rnp=ROW_NUMBER() OVER (PARTITION BY NUM ORDER BY ID) FROM @T)
,grouped AS (SELECT *, grp=ROW_NUMBER() OVER (PARTITION BY rn-rnp ORDER BY ID) FROM numbered)
SELECT ID, NUM, [rank]=DENSE_RANK() OVER (ORDER BY rn-grp)
FROM grouped
ORDER BY ID;
Cheers!
EDIT: This post is a duplicate of https://www.sqlservercentral.com/Forums/1889629/Ranking-For-each-group
Posting the same question in multiple places hurts more than it helps. I'd recommend just posting in the forum appropriate to your version of SQL Server in the future.
July 31, 2017 at 5:23 pm
Thank You Very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply