Row_number() in case statement returning unexpected results

Question

Row_number() in case statement returning unexpected results

klopeks

SSC Veteran

Points: 201
More actions
July 25, 2017 at 9:19 am

#331984

I've got a long query that contains this
row_number() over (partition by a.accountnumber, a.closedChatDate order by a.requestedTime) as AcctNumberCount
I tried putting it in a case statement like this:
case
when row_number() over (partition by a.accountnumber, a.closedChatDate order by a.requestedTime)=1
then 1 else 0 end AcctCnt
now I can see for records where the first statement is in fact returning a 1, it sometimes, but not always, puts a 1 in the second expression. If the first expression definitvely evaluates to 1, why would the case statement containing the same not always return a 1 ?

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

klopeks - Tuesday, July 25, 2017 9:19 AM
I've got a long query that contains this
row_number() over (partition by a.accountnumber, a.closedChatDate order by a.requestedTime) as AcctNumberCount
I tried putting it in a case statement like this:
case
when row_number() over (partition by a.accountnumber, a.closedChatDate order by a.requestedTime)=1
then 1 else 0 end AcctCnt
now I can see for records where the first statement is in fact returning a 1, it sometimes, but not always, puts a 1 in the second expression. If the first expression definitvely evaluates to 1, why would the case statement containing the same not always return a 1 ?

Please post sample data and an actual statement that exhibits your assertion.
Also, have you run the query without the case to ensure that it is actually giving you values you expect in all cases?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

klopeks SSC Veteran Points: 201 More actions · Answer 2

well, I had both of those statements in the same query so I could look at each row. There were a lot of times that there was a 1 in the first field, but not in the 2nd field.
I walked away for a few hours to do something else, and when trying to produce some examples to post here, I re-ran the query and somehow now it seems to be working. I didn't change any code, but clearly *something* has changed. So it looks like it's working for now.

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

This will happen if the ORDER BY clause in your OVER() clause is non-deterministic. That is, you have two or more rows that have the same value for the partition/order combination and it randomly arranges the rows within that subset of records which means that the order is not guaranteed, so it can--and will--vary each time it is evaluated. The way to correct this is to add fields to the order by to make the order deterministic (usually by adding the primary key field at the end).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA