SQL - ROW_NUMBER () OVER (ORDER BY) not working

Question

It doesn't matter what I put in the ORDER BY clause, the order of the result set doesn't change.

WITH Results AS (
    SELECT DISTINCT 
        MessageThreadUsers.threadFK, 
        MessageThreads.threadDate, 
        Messages.MessageBody, 
        Messages.senderFK,
        Users.userFullName AS senderFullName, 
        ROW_NUMBER() OVER (ORDER BY MessageThreads.threadDate DESC) AS RowNumber

    FROM MessageThreadUsers
        JOIN MessageThreads ON MessageThreadUsers.threadFK = MessageThreads.threadID
        JOIN Messages ON MessageThreads.threadDate = Messages.messageDate
        JOIN Users ON Messages.senderFK = Users.userID

    WHERE userFK = 'usr_developer'
) 
SELECT * FROM Results WHERE RowNumber BETWEEN 1 AND 10

The ORDER BY in the OVER clause is for the ROW_NUMBER only -- the ORDER BY is the only means of controlling the order of the final result set. — OMG Ponies, Mar 30 '12 at 04:17
I thought that this construct is how you page through results. Can you suggest how I would page this query in SQL 2008? — Redtopia, Mar 30 '12 at 04:21
@JohnSaunders: out of interest, if you call SQL Server "the database", what do you call the database (such as AdventureWorks)? — onedaywhen, Mar 30 '12 at 08:17
@onedaywhen: I was in a hurry. I should have said, "database system" or something like that. — John Saunders, Mar 30 '12 at 14:41

RBarryYoung · Accepted Answer · 2012-03-30T04:26:38.093

8

ORDER BY only controls the order of returned rows when it is applied to the outermost SELECT statement.

This may work better:

WITH Results AS (
    SELECT DISTINCT 
        MessageThreadUsers.threadFK, 
        MessageThreads.threadDate, 
        Messages.MessageBody, 
        Messages.senderFK,
        Users.userFullName AS senderFullName, 
        ROW_NUMBER() OVER (ORDER BY MessageThreads.threadDate DESC) AS RowNumber

    FROM MessageThreadUsers
        JOIN MessageThreads ON MessageThreadUsers.threadFK = MessageThreads.threadID
        JOIN Messages ON MessageThreads.threadDate = Messages.messageDate
        JOIN Users ON Messages.senderFK = Users.userID

    WHERE userFK = 'usr_developer'
) 
SELECT * FROM Results WHERE RowNumber BETWEEN 1 AND 10
ORDER BY RowNumber

edited Mar 30 '12 at 04:26

answered Mar 30 '12 at 04:16

RBarryYoung

55,398
14
96
137

OK, I guess I don't understand how I'm supposed to use this construct to page result sets. Can you offer a suggestion to page these results? – Redtopia Mar 30 '12 at 04:19
I SEE! I didn't notice the new "ORDER BY ThreadDate" at the bottom of your answer. – Redtopia Mar 30 '12 at 04:29
2

I just updated it. ORDER BY RowNumber is better in this case. – RBarryYoung Mar 30 '12 at 04:33

SQL - ROW_NUMBER () OVER (ORDER BY) not working

1 Answers1