My row_number() is misbehaving or I'm an not a smart man
-
OK I've got some sample setup and data below; basically i need a row_number() i've aliased as [BuildingID], to be a unique value for each value in [ACTV_HOME_PROPERTY_ID] to be generated; if there was two unique values in those five rows, i'd expect values of 1 or 2, but i'm not doing something right today.
in the sample data below, there is only ONE unique value, so I expect only the value [1] for all five rows;
I know i'm pre-coffee, but i keep getting the same values in row number, no matter what i partition by;
CREATE TABLE [dbo].[#MYSAMPLEDATA] (
[ACTNBR] VARCHAR(50) NULL,
[ACTNAME] VARCHAR(100) NULL,
[RELATEDYEAR] VARCHAR(4) NULL,
[IDIS_ACTV_ID] VARCHAR(12) NULL,
[ACTV_HOME_PROPERTY_FUND_ID] VARCHAR(15) NULL,
[ACTV_HOME_PROPERTY_ID] VARCHAR(15) NULL,
[FUND_CAT_ID] VARCHAR(15) NULL)
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315449','900000000105337','122')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000329445','900000000105337','22')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315451','900000000105337','302')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315452','900000000105337','303')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315450','900000000105337','21')
--edit: adding five other rows
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315449','900000000105338','122')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000329445','900000000105338','22')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315451','900000000105338','302')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315452','900000000105338','303')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315450','900000000105338','21')
SELECT
1 As TheExpectedValue,
IDIS_ACTV_ID AS ID,
ROW_NUMBER() OVER (PARTITION BY IDIS_ACTV_ID ORDER BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID) AS BUILDINGID,
ROW_NUMBER() OVER (PARTITION BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID ORDER BY IDIS_ACTV_ID) AS BUILDINGID_V2,
ROW_NUMBER() OVER (PARTITION BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID ORDER BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID) AS BUILDINGID_V3,
Z.ACTNBR,
Z.ACTNAME,
Z.RELATEDYEAR,
Z.IDIS_ACTV_ID,
Z.ACTV_HOME_PROPERTY_FUND_ID,
Z.ACTV_HOME_PROPERTY_ID,
Z.FUND_CAT_ID
FROM #MYSAMPLEDATA Z
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
wow amazing what a little coffee can do for me!
my issue was the wrong tool for the job.
i needed to use DENSE_RANK() and not ROW_NUMBER
this is what i should have used:
SELECT
1 As TheExpectedValue,
IDIS_ACTV_ID AS ID,
DENSE_RANK() OVER (PARTITION BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID ORDER BY IDIS_ACTV_ID) AS BUILDINGID,
Z.ACTNBR,
Z.ACTNAME,
Z.RELATEDYEAR,
Z.IDIS_ACTV_ID,
Z.ACTV_HOME_PROPERTY_FUND_ID,
Z.ACTV_HOME_PROPERTY_ID,
Z.FUND_CAT_ID
FROM #MYSAMPLEDATA Z
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I was working on this when you posted your solution and had also tried the ROW_NUMBER, RANK and DENSE_RANK, but it wasn't returning what I thought were the results you wanted when you added in another row with a different ACTV_HOME_PROPERTY_ID value. I was scratching my head when you posted your resolution. Are you sure this is what you're after?
And yes, coffee is a great help to all of us. 🙂
-
Ed you are correct, i'm still not getting what i'm after;
if i add these five more rows to my sample data above, i would expect five values with [1] and five values with[2], but i end up with all [1] values
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315449','900000000105338','122')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000329445','900000000105338','22')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315451','900000000105338','302')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315452','900000000105338','303')
INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315450','900000000105338','21')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Maybe this?
DENSE_RANK() OVER (ORDER BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID) AS BUILDINGID,____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I came up with this.
SELECTIDIS_ACTV_ID AS ID,
Z.ACTV_HOME_PROPERTY_ID,
ROW_NUMBER() OVER (PARTITION BY ACTV_HOME_PROPERTY_ID ORDER BY (SELECT NULL)) AS BUILDINGID,
Z.ACTNBR,
Z.ACTNAME,
Z.RELATEDYEAR,
Z.IDIS_ACTV_ID,
Z.ACTV_HOME_PROPERTY_FUND_ID,
Z.FUND_CAT_ID
FROM #MYSAMPLEDATA Z;
It builds a series for each ACTV_HOME_PROPERTY_ID and restarts for each unique value. I must admit, however, that I'm questioning whether or not I'm understanding the requirements properly.
-
Mark-101232 (6/13/2013)
Maybe this?DENSE_RANK() OVER (ORDER BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID) AS BUILDINGID,Adding anything else in the order by will screw the DENSE_RANK if they aren't all the same. This should do it based on how I've understood the requirement:
DENSE_RANK() OVER (ORDER BY ACTV_HOME_PROPERTY_ID) AS BUILDINGID_V4
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply