CODEWITHSUNDEEP
sql-server
CJ Dennis
CJ Dennis

Reputation: 4356

SQL Server increment the row number for each new unique value

I'm trying to get results like the following:

1 foo 1-Jan
2 bar 2-Feb
3 baz 3-Mar
3 baz 4-Apr
3 baz 5-May
4 quz 6-Jun
4 quz 7-Jul

with this T-SQL:

SELECT ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [DateTime]) AS [SetNum], *
FROM [ComplicatedQuery]

But instead I'm getting:

1 foo 1-Jan
1 bar 2-Feb
1 baz 3-Mar
2 baz 4-Apr
3 baz 5-May
1 quz 6-Jun
2 quz 7-Jul

How can I change it to increment the row/set number for each new unique value of [ID]?

Upvotes: 0

Views: 3182

Answers (3)

Navneet
Navneet

Reputation: 447

SELECT Distinct Dense_Rank() OVER (ORDER BY ID) AS [SetNum], ID into #temp
FROM [ComplicatedQuery]


SELECT t.SetNum AS [SetNum], cq.* 
FROM [ComplicatedQuery] cq join #temp t on cq.ID=t.ID order by t.SetNum

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

I would use DENSE_RANK() instead of ROW_NUMBER() thus:

DENSE_RANK() OVER(ORDER BY [Column_with_foo_bar_baz_coco_jambo_etc]) AS Rnk

Tip: look at example A. Ranking rows within a partition from documentation: enter image description here

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

We need to use two windowed aggregates to achieve your desired results - the first ensures that all rows for the same ID get assigned a single value to then use in the ordering criteria:

declare @t table (Desired int,ID varchar(9),Dt datetime)
insert into @t(Desired,ID,Dt) values
(1,'foo','20170101'),
(2,'bar','20170202'),
(3,'baz','20170303'),
(3,'baz','20170404'),
(3,'baz','20170505'),
(4,'quz','20170606'),
(4,'quz','20170707')

;With MinDt as (
select *,MIN(Dt) OVER (PARTITION BY ID) as MinDt
from @t
)
select *,DENSE_RANK() OVER (ORDER BY MinDt,ID) as rk
from MinDt

Results:

Desired     ID        Dt                      MinDt                   rk
----------- --------- ----------------------- ----------------------- --------------------
1           foo       2017-01-01 00:00:00.000 2017-01-01 00:00:00.000 1
2           bar       2017-02-02 00:00:00.000 2017-02-02 00:00:00.000 2
3           baz       2017-03-03 00:00:00.000 2017-03-03 00:00:00.000 3
3           baz       2017-04-04 00:00:00.000 2017-03-03 00:00:00.000 3
3           baz       2017-05-05 00:00:00.000 2017-03-03 00:00:00.000 3
4           quz       2017-06-06 00:00:00.000 2017-06-06 00:00:00.000 4
4           quz       2017-07-07 00:00:00.000 2017-06-06 00:00:00.000 4

The inclusion of ID in the DENSE_RANK is to act as a tie-breaker in case multiple ID values have the same minimal Dt value.

Upvotes: 0

Related Questions