SQL Server Multiple Sorting Clarification-CodePudding

Query 1:

SELECT
    ROW_NUMBER() OVER(PARTITION BY C_CallID ORDER BY DATETIME) ROWNUMBER
    ,CallID
    ,C_CALLID
    ,NAME
    ,DATETIME
FROM
    TABLENAME
ORDER BY DATETIME

Query 2:

SELECT
    ROW_NUMBER() OVER(PARTITION BY C_CallID ORDER BY DATETIME, ASC NAME DESC) ROWNUMBER
    ,CallID
    ,C_CALLID
    ,NAME
    ,DATETIME
FROM
    TABLENAME
ORDER BY DATETIME ASC, NAME DESC

Query 1 Output:

Query 2 Output:

So my understanding is Rownumber will be sorting based on DATETIME ASC for all the records and then Sorting will be DESC for NAME column records.

My question here is "Will second sorting affects first sorted data in anyway?"

CodePudding user response：

It's always easier to explain using a simple example.
consider the following data:

CREATE TABLE Sample (
  A int,
  B int
);

INSERT INTO Sample (A, B) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 1),
(3, 2),
(3, 3);

And the following queries:

SELECT A, B
FROM Sample
ORDER BY A ASC, B ASC;

A   B
1   1
1   2
1   3
2   1
3   1
3   2
3   3


SELECT A, B
FROM Sample
ORDER BY A ASC, B DESC;

A   B
1   3
1   2
1   1
2   1
3   3
3   2
3   1

Both queries will result with A in ascending order, but the first will have B in ascending order as well, while the second with have B in descending order - So the second sort does not affect the first one in any way. The secondary sort only kicks in when the primary sort returns multiple rows with equal values in that column.
Of course, that rule applies even if you have an order by clause that contains more than two columns.