My row_number() is misbehaving or I'm an not a smart man

Question

Post reply

My row_number() is misbehaving or I'm an not a smart man

Viewing 6 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323741 More actions · Answer 1

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!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

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. 🙂

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323741 More actions · Answer 3

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!

Mark Cowne One Orange Chip Points: 26986 More actions · Answer 4

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

I came up with this.

SELECT

IDIS_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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

HowardW One Orange Chip Points: 29109 More actions · Answer 6

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