The below query is outputting two rows. Given that I am partitioning on ref_number and also have ref_number in the where clause, I believe I should only ever get a single row back.
WITH rank_cte AS (SELECT ref_number, RANK() OVER( PARTITION BY ref_number ORDER BY Logged_Date DESC ) AS "Rank1" From my_table ) SELECT * FROM rank_cte WHERE Rank1= 1 and ref_number = 'abcd'
What is likely to have caused this? SQL Server must think they are similar enough for the where clause to return both rows, but different enough to partition them.