MS-SQL: "Desc" seems to have no effect on "order by" if used in a subquery

Question

Yes, the right thing is to use "order by" in the calling query instead of the subquery, but not in this case. I want to order a list in descending order of date, but I don't want to show the actual date field itself in the output. I just want to show the "spoken" date, e.g. Thu 21 Jul, Wed 20 Jul, etc.

with list1 as
(
select
top 100 percent
convert(varchar,Sign_in,23) [Date],
datename(d,Sign_in)+' '+
left(datename(m,Sign_in),3)+' '+
left(datename(dw,Sign_in),3) [Day],
string_agg(trim(uid),' ') Staff

from
attendance.staff with(nolock)

where
Sign_in >= getdate() - 14 

group by
datename(d,Sign_in)+' '+
left(datename(m,Sign_in),3)+' '+
left(datename(dw,Sign_in),3),
convert(varchar,Sign_in,23)

order by
[Date] DESC
)
select
Day,
Staff
from
list1

Whether I have the "desc" or not, the list is always sorted in ascending order. If I take the subquery out and run it separately, "desc" or "asc" behave as expected. But used inside the subquery, "desc" has no effect.

Example output:

8 Jul Friday John Mary Amy
11 Jul Monday Mary Jack
12 Jul Tuesday John Mary
13 Jul Wednesday Karen Ian
14 Jul Thursday Martin Suzanne Mary John
15 Jul Friday etc. etc. etc.
18 Jul Monday etc. etc. etc.
19 Jul Tuesday etc. etc. etc.
20 Jul Wednesday etc. etc. etc.
21 Jul Thursday etc. etc. etc.

I want the above to list in descending order from 21 Jul to 8 Jul

Answer 1

WITH List1 AS (
    SELECT Sign_In [Date],
           string_agg(trim(uid),' ') [Staff]
    FROM Attendance
    WHERE Sign_In >= GetDate()-14
    GROUP BY Sign_In
)
SELECT FORMAT([Date], 'dd dddd MMMM'), -- 07 Thursday July
       [Staff]
FROM   List1
ORDER BY [Date] Desc

~ Assuming Sign_In is a DateTime or something similar, if it is a Date, you can omit the Convert. ~ (Convert removed per OP's comment)

And unless you have good reason, never use (NOLOCK).

EDIT 2:

Actually, given that Sign_In is a Date, there's no longer need for the CTE.

SELECT format(Sign_In, 'dd dddd MMMM') [Date],
       string_agg(trim(uid), ' ') [Staff] 
FROM   Attendance
WHERE  Sign_In >= GetDate()-14
GROUP BY Sign_In
ORDER BY Sign_In DESC