SQL Server 2005 - Row_Number()

Question

Hi All,

I'm trying to understand the unusual behaviour seen when ordering results in a descending order using the row_number() function when using a DISITINCT on the outermost select in my query as below:

 SELECT   DISTINCT (ID), State_Id, Name_Of_Trip, Date_Of_Travel, Creation_Date, Locking_Id, Applicant_Name, Reference_Number, State_Name
    FROM (

    SELECT  app.ID, app.State_Id, app.Name_Of_Trip, app.Date_Of_Travel, app.Creation_Date, app.Locking_Id, app.Applicant_Name, app.Reference_Number, 
    State.Name AS State_Name, ROW_NUMBER() OVER(ORDER BY Reference_Number DESC) as rowNum  
    FROM Application_Leg AS app 
    INNER JOIN State AS state 
    ON app.State_Id = state.ID
    WHERE  (app.State_Id = 5 OR app.State_Id = 6 OR app.State_Id = 8)  AND app.Organisation_Id=12 
    AND Leg_Number IN
     (SELECT  DISTINCT Leg_Number 
     from Application_Leg as al
     INNER JOIN
     Organisation as org
     ON al.Organisation_Id = org.ID
     WHERE al.ID=app.ID AND org.Approval_Required=1 AND Mode_Of_Transport=1))
     as pagedApplications
    WHERE rowNum BETWEEN 0 AND (0 + 10)

When the outermost DISTINCT is taken out then the descending order is fine but when it is included the results are not shown in descending order.

Answer 1

Have you tried adding an order by to your outer select?

Answer 2

ORDER BY in ROW_NUMBER clause does not guarantee the order of the resultset.

ROW_NUMBER usually uses sorting in the query plan which results in the fact that the values come out presorted.

This is a side effect and should not be relied upon.

DISTINCT uses Hash Match (Aggregate) which breaks sorting.

Add ORDER BY clause to the end of the query:

SELECT  DISTINCT (ID), State_Id, Name_Of_Trip, Date_Of_Travel, Creation_Date, Locking_Id, Applicant_Name, Reference_Number, State_Name
FROM    (
        SELECT  app.ID, app.State_Id, app.Name_Of_Trip, app.Date_Of_Travel,
                app.Creation_Date, app.Locking_Id, app.Applicant_Name, app.Reference_Number, 
                State.Name AS State_Name, ROW_NUMBER() OVER(ORDER BY Reference_Number DESC) as rowNum  
        FROM    Application_Leg AS app 
        INNER JOIN
                State AS state 
        ON      app.State_Id = state.ID
        WHERE   app.State_Id IN (5, 6, 8)
                AND app.Organisation_Id = 12 
                AND Leg_Number IN
                (
                SELECT  Leg_Number 
                FROM    Application_Leg as al
                INNER JOIN
                        Organisation as org
                ON      al.Organisation_Id = org.ID
                WHERE   al.ID = app.ID
                        AND org.Approval_Required = 1
                        AND Mode_Of_Transport = 1
                )
        ) AS pagedApplications
WHERE   rowNum BETWEEN 0 AND (0 + 10)
ORDER BY
        ReferenceNumber DESC

Also note that it will not return 10 distinct results, it will return DISTINCT of the first 10 results.

If you want the former, use this:

SELECT  DISTINCT TOP 10 ID, State_Id, Name_Of_Trip, Date_Of_Travel, Creation_Date, Locking_Id, Applicant_Name, Reference_Number, State_Name
FROM    (
        SELECT  app.ID, app.State_Id, app.Name_Of_Trip, app.Date_Of_Travel,
                app.Creation_Date, app.Locking_Id, app.Applicant_Name, app.Reference_Number, 
                State.Name AS State_Name
        FROM    Application_Leg AS app 
        INNER JOIN
                State AS state 
        ON      app.State_Id = state.ID
        WHERE   app.State_Id IN (5, 6, 8)
                AND app.Organisation_Id = 12 
                AND EXISTS
                (
                SELECT  Leg_Number 
                FROM    Application_Leg AS al
                INNER JOIN
                        Organisation as org
                ON      al.Organisation_Id = org.ID
                WHERE   al.ID = app.ID
                        AND al.LegNumber = app.LegNumber
                        AND org.Approval_Required = 1
                        AND Mode_Of_Transport = 1
                )
        ) AS pagedApplications
ORDER BY
        ReferenceNumber DESC