CODEWITHSUNDEEP
sqlsql-server-2008
quant
quant

Reputation: 23022

Why is ORDER BY not sorting this query?

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).

Upvotes: 0

Views: 105

Answers (1)

user172839
user172839

Reputation: 1065

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.

Upvotes: 1

Related Questions