I am a little confused as to where would be the preferred place to add the condition when using CTE with ROW_NUMBER OVER PARTITION.
I have a table containing the following columns:
UserID, BranchNumber, MemberDate and MemberStatus
Note: A member can have multiple memberships at different locations:
The following code gives me one less record: 17069
WITH CTE AS
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY [MemberDate] DESC) AS RowNumber
FROM MemberTable
WHERE BranchNumber = '01'
)
SELECT * FROM CTE WHERE RowNumber = 1 AND MemberStatus = 'Active'
The following code gives one additional record: 17070
WITH CTE AS
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY [MemberDate] DESC) AS RowNumber
FROM MemberTable
WHERE BranchNumber = '01' AND MemberStatus = 'Active'
)
SELECT * FROM CTE WHERE RowNumber = 1
I am just confused as to why the difference and which is the right way?
The correct amount of records is 19000.