0

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.

Improve this question
2
  • I have other rows in the data where this happens and returns 3 rows.
    – Neil P
    Commented Feb 17, 2015 at 10:59
  • 2
    Rank will return the same number for ties. You should compare the output of RANK(), DENSE_RANK() and ROW_NUMBER() to see which one meets your needs.
    – Aaron Bertrand
    Commented Feb 17, 2015 at 13:24

1 Answer 1

Reset to default
1

Found the problem, there were ties in the rank ordering, hence why multiple rows are returned with the same rank id.

The solution will be for me to find an ordering key that has unique combinations of partition and order key.

Improve this answer
3
  • 3
    Or, if you're happy for SQL Server to completely arbitrarily pick a winner from two identical rows, use ROW_NUMBER rather than RANK.
    – AakashM
    Commented Feb 17, 2015 at 15:58
  • Very true, I probably should have mentioned that in my question. For my current situation, I cant have that, so have changed the sort key I am using.
    – Neil P
    Commented Feb 17, 2015 at 16:17
  • @Neil If you have ties on ref_number and logged_date, what exactly is it that you "can't have"? How do you know the ties are presented in the wrong order? If some other column breaks the tie, then you can just add it to the order by, not the key.
    – Aaron Bertrand
    Commented Feb 18, 2015 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.