Why is ORDER BY not sorting this query?

Question

I am using WITH SELECT INTO to build a new table, and I want the values sorted in descending order based on one of the FLOAT columns. Here is the raw code (the last line is where I try to order the result):

WITH attributionErrorCandidates AS 
(
    SELECT 
        a.ID aID,
        a.FREQUENCY aFREQ,
        MAX(dbo.JaroWInkler(a.NAME,b.NAME)) score,
        ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY MAX(dbo.JaroWInkler(a.NAME,b.NAME)) DESC) RN,
        b.ID bID,
        b.FREQUENCY bFREQ
    FROM #brandDivisionAttributionCounts a
    JOIN #brandDivisionAttributionCounts b
    ON a.BRAND_LEVEL = b.BRAND_LEVEL
    AND a.DIVISION = b.DIVISION
    AND a.ID <> b.ID
    AND a.NAME <> b.NAME
    GROUP BY a.ID,b.ID,a.FREQUENCY,b.FREQUENCY
)
SELECT
    bdac.NAME AS NAME,
    bdac.FREQUENCY AS NAME_FREQUENCY,
    bdac2.NAME AS OTHER_NAME,
    bdac2.FREQUENCY AS OTHER_NAME_FREQUENCY,
    score AS CONFIDENCE,
    #brandLevels.NAME AS BRAND_LEVEL,
    bdac.DIVISION AS DIVISION
INTO ArmanS.attributionErrorRankings
FROM attributionErrorCandidates
INNER JOIN #brandDivisionAttributionCounts AS bdac
    ON bdac.ID = aID
    AND RN = 1
    AND score > @scoreCutOff
INNER JOIN #brandDivisionAttributionCounts AS bdac2
    ON bdac2.ID = bID
INNER JOIN #brandLevels
    ON bdac.BRAND_LEVEL = #brandLevels.ID
WHERE bdac.FREQUENCY / bdac2.FREQUENCY > @requiredRatio
ORDER BY score DESC -- doesn't seem to work!

I have tried replacing score with cast(score AS FLOAT) and DIVISION to no avail. The result is not ordered by the score (called DIVISION in the resulting table).

Have a look at the accepted answer to this question, http://stackoverflow.com/questions/14424929/preserving-order-by-in-select-into — jac, Jan 16 '14 at 05:01
It is important to remember that caveat that the only "natural order" is the clustered index, because we can use that. When optimizing performance, it is huge to understand and leverage that natural order because an Order By (==Sort) is a very expensive operation. For example, if I was making a table like yours as a temp table, and I was wanted to use it more than once with the same order, I might make a clustered index of the key columns, before the Insert - thereby sorting it only once (during that insert). — Mike M, Jan 16 '14 at 05:13
@MikeM - there's nothing special about the clustered index, per se. *any* index, if it's covering and has the same sort order as that requested by the `ORDER BY` clause may help the server avoid needing to perform an actual sort operation. — Damien_The_Unbeliever, Jan 16 '14 at 07:13

score 1 · Accepted Answer · answered Jan 16 '14 at 05:02

1

There is no guarantee to what orders the results will be returned with a select statement. Just put an order by in your select statement.

answered Jan 16 '14 at 05:02

user172839

983
1
9
19

1

There *is* an ORDER BY on the select statement (albeit one which is "useless" in the given code). The issue is that the *later usage* of `ArmanS.attributionErrorRankings` is not implicitly ordered. – user2864740 Jan 16 '14 at 05:06

Why is ORDER BY not sorting this query?

1 Answers1