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