SQL help with partition

Don75 81 Reputation points
2021-01-27T16:37:24.367+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,651 questions
0 comments No comments
{count} votes

Accepted answer
  1. Guoxiong 8,206 Reputation points
    2021-01-27T17:17:08.093+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Don75 81 Reputation points
    2021-01-27T18:24:54.217+00:00

    Thanks, that solved the issue.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.