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()
            PARTITION BY
            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.

  • I have other rows in the data where this happens and returns 3 rows. – Neil P Feb 17 '15 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 Feb 17 '15 at 13:24

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.

  • 2
    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 Feb 17 '15 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 Feb 17 '15 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 Feb 18 '15 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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