Undetermined sort order when using ROW_NUMBER() OVER decimal column with ties

Question

I have a very strange bug with sql server 2008:

To display a pager in a CRUD application details view, I make a sql request to get previous and next record relative to the current, it works well except when ordering on a DECIMAL column, I can reduce the problem to that query (FTE is decimal(18,2)):

WITH [IndexedRows] AS (
    SELECT 
        [ContactId], 
        [ContactCode],
        [FTE],
        [EmployeeName], 
        ROW_NUMBER() OVER ( ORDER BY [FTE] ASC ) AS [RowIndex]
    FROM   
        [Vw_HrEmployee]
)

/* 1. I can see ContactId 1109 is rowindex 7 */
/*SELECT * FROM [IndexedRows];*/

/* 2. Get the RowIndex, it returns 7 */
/*SELECT [RowIndex] 
FROM   [IndexedRows] 
WHERE  [ContactId] = 1109;*/

/* 3. Why it doesn't returns ContactId 1109 ??? */
SELECT [ContactId], 
       [EmployeeName] 
FROM   [IndexedRows] 
WHERE [RowIndex] = 7;

I get the contactId of another person who have the same FTE value, but I don't understand why the WHERE rowindex don't return the correct row (if I display IndexedRows, it looks good!).

I don't think it's revelant but Vw_HrEmployee is a View.

Any help/idea/workaround to solve that is welcome,

Thanks in advance

You say "I get the contactId of another person who have the same FTE value" If you have ties for the `FTE` value it will be undeterministic how they are numbered within the tied group so this is expected behaviour. — Martin Smith, Oct 27 '11 at 16:47
What's your patch level? I remember running into issues with SQL 2008 CTE's prior to CU5(?). Although I don't think we've had any issues since SP2. — NotMe, Oct 27 '11 at 16:50
Are you 100% positive all FTE values are unique and thus create a deterministic sort order? Any duplicate value and your ROW_NUMBER() output is undetermined. — Remus Rusanu, Oct 27 '11 at 16:50
`select isn't broken`: http://www.codinghorror.com/blog/2004/10/a-pragmatic-quick-reference.html — Remus Rusanu, Oct 27 '11 at 16:55
you were right the sorting wasn't deterministic, I shoudn't rely on the fact that sql server show me a certain order. — Guillaume86, Oct 27 '11 at 16:58
@Remus I did already find some bugs in the .NET framework for example, so it doesn't hurt to ask ;) — Guillaume86, Oct 27 '11 at 17:01
The edit basically put the answer in the question, not sure it helps for people looking for the same issue — Guillaume86, Jun 22 '12 at 13:24

score 6 · Accepted Answer · answered Oct 27 '11 at 16:50

6

This is a guess, but if you have duplicate values for the FTE value then there is no guarantee as to which order they will come out in each time that the code is run. SQL Server is probably changing the query plan between when you do a simple SELECT and a SELECT with the WHERE criteria, causing a different order to come out and what you are seeing.

I would add the PK onto the end of your ORDER BY just to ensure that the ordering is always consistent.

answered Oct 27 '11 at 16:50

Tom H

46,766
14
87
128

1

This is exactly that! I accept your answer since the sorting on primary key idea is also very handy in my case, thanks! – Guillaume86 Oct 27 '11 at 16:55

score 5 · Answer 2 · answered Oct 27 '11 at 16:44

5

Perhaps add another sort criteria to help ensure the correct/desired order?

ROW_NUMBER() OVER ( ORDER BY [FTE] ASC, 
                             BirthDate ASC, 
                             AstrologySign DESC ) AS [RowIndex]

answered Oct 27 '11 at 16:44

p.campbell

98,673
67
256
322

thanks for the answer, actually the sorting depends on how the user did sorted a grid in the previous page, I think it will be problematic to change it, but I'll try since it should make all rows sorting explicit) – Guillaume86 Oct 27 '11 at 16:49

Undetermined sort order when using ROW_NUMBER() OVER decimal column with ties

2 Answers2

Linked