I think the problem might be the ORDER BY clause since the rows of both columns in ORDER BY are the same within the PARTITION the ROW_NUMBER() generates 1 for the row which is random within the partition. You need to add another column in the ORDER BY clause.
SQL help with partition
I have an sql issue which is difficult to simulate here in the forum with sample data.
My code has the following.
CASE ROW_NUMBER() OVER(PARTITION BY t1.PreMR,t1.AccNumber ORDER BY t1.PreMR,t1.AccNumber )
When 1 THEN 1
ELSE 0
END as [IncludeForCalculation]
With this I am expecting to see 1 for each new t1.PreMR,t1.AccNumber.
Let's say I have the following data
PreMR, AccNumber
B123, A123
B123, A123
B123, A123
B123, A123
B123, A222
B123, A222
B222, A678
B222, A678
B222, A678
B222, A678
B222, A678
B333, A888
B333, A888
I am expecting to see 1 for the first occurence of t1.PreMR,t1.AccNumber
PreMR, AccNumber, IncludeForCalculation
B123, A123, 1
B123, A123, 0
B123, A123, 0
B123, A123, 0
B123, A222, 1
B123, A222, 0
B222, A678, 1
B222, A678, 0
B222, A678, 0
B222, A678, 0
B222, A678, 0
B333, A888, 1
B333, A888, 0
However, I noticed that this doesn't work all the time. Each time I run the query, my results are different. Some times it puts the 1 for each new rec and the other times it doesn't. In my report, I am totaling the IncludeForCalculation column. Each time I run the query my totals are different. I get 4, 3 , 2, 3, 4...etc.
I am not sure how to fix this. Maybe how I am using the code is wrong.
Any help is greatly appreciated.
1 additional answer
Sort by: Most helpful
-
Don75 81 Reputation points
2021-01-27T18:24:54.217+00:00 Thanks, that solved the issue.