Row number not exactly working

Question

Row number not exactly working

Bhagat

SSC-Addicted

Points: 405
More actions
August 8, 2019 at 10:50 am

#3669811

Using row number function with partitioning, I do not get the desired result.
Attached image shows what the code is outputting at the moment. The last column (Expected) is what I need the values to show.
Rows 1-3 show correct row number (RID).
Rows 4-17 show correct row number (RID).
Row 18 is the problem, it continues from 1st partition because it has the value of 1 in the PropertyStatusKey column. I would like it to reset and restart the numbering.
Is this possible to do?
My select statement is:
select DateKey, PropertyKey, PropertyStatusKey, RID = ROW_NUMBER () OVER (Partition BY PropertyKey, PropertyStatusKey ORDER BY DateKey)
Attachments:
You must be logged in to view attached files.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

@Taps SSC Eights! Points: 879 More actions · Answer 1

I don't think you can using current partition - you will have to add some other the condition in partition - basically how do you determine row 18 should reset

for example if this is based on date key then a case statement based on date

RID = ROW_NUMBER () OVER (Partition BY PropertyKey, PropertyStatusKey , case when datekey < ... then 0 else 1 end ORDER BY DateKey)

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

I don't have the answer to this, but I think it's a "gaps and islands" problem. Try searching for one of the articles that's been written on that.

John

drew.allen SSC Guru Points: 76985 More actions · Answer 3

SSMS doesn't have built-in OCR, so a picture is worthless. You should post consumable sample data like below.

DROP TABLE IF EXISTS #YourTable;

CREATE TABLE #YourTable
(
DateKeyINTPRIMARY KEY
,Property_KeyINT
,Property_Status_KeyBIT
);

INSERT #YourTable (DateKey, Property_Key, Property_Status_Key)
VALUES
(20160401, 27758, 1)
,(20160402, 27758, 1)
,(20160403, 27758, 1)
,(20160404, 27758, 0)
,(20160405, 27758, 0)
,(20160406, 27758, 0)
,(20160407, 27758, 0)
,(20160408, 27758, 0)
,(20160409, 27758, 0)
,(20160410, 27758, 0)
,(20160411, 27758, 0)
,(20160412, 27758, 0)
,(20160413, 27758, 0)
,(20160414, 27758, 0)
,(20160415, 27758, 0)
,(20160416, 27758, 0)
,(20160417, 27758, 0)
,(20160418, 27758, 1)
,(20160419, 27758, 1)
,(20160420, 27758, 1)
,(20160421, 27758, 1)
,(20160422, 27758, 1)
,(20160423, 27758, 1)
,(20160424, 27758, 1)
,(20160425, 27758, 1)
,(20160426, 27758, 1)
,(20160427, 27758, 1)
,(20160428, 27758, 1)
,(20160429, 27758, 1)
,(20160430, 27758, 1)
,(20160501, 27758, 1)
,(20160502, 27758, 1)
,(20160503, 27758, 1)
,(20160504, 27758, 1)
,(20160505, 27758, 1)
,(20160506, 27758, 1)
,(20160507, 27758, 1)
,(20160508, 27758, 1)
;

This allows people to get started almost immediately, which greatly improves your chances of getting a TESTED answer.

John is correct that this is a gaps and islands problem. Here is a sample solution.

WITH RowGroups AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY yt.Property_Key ORDER BY yt.DateKey) - ROW_NUMBER() OVER(PARTITION BY yt.Property_Key, yt.Property_Status_Key ORDER BY yt.DateKey) AS grp
FROM #YourTable AS yt
)
SELECT rg.DateKey, rg.Property_Key, rg.Property_Status_Key, ROW_NUMBER() OVER(PARTITION BY rg.Property_Key, grp ORDER BY rg.DateKey) AS RID
FROM RowGroups rg
ORDER BY rg.DateKey;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Row number not exactly working

Attachments: