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

If you want sorted output ORDER BY should be done in the outer query — Sergey, Jul 21 '22 at 06:32
"I don't want to show the actual date field itself in the output" - that by no means stops you from using it in the only `ORDER BY` clause which affects output order, which is the one placed on the outermost query. `ORDER BY` columns do *not* need to be in the `SELECT` clause. — Damien_The_Unbeliever, Jul 21 '22 at 06:37
Why are you concerning the date, which you say you don't want, to `varchar` here too? — Thom A, Jul 21 '22 at 06:41
Yes everyone, I know ORDER BY would typically go in the outer query, but as stated in the first part of my question, I don't want to show the date (the one used for reverse sorting) in the final output. That is why I want to (have to) use it in the subquery. — Tunc Gercek, Jul 21 '22 at 07:26
As you have been told by others and seem not to have taken in you don't have to use it in the subquery. Read Damian's comment again or look at the answer given — Martin Smith, Jul 21 '22 at 07:37
`SELECT A FROM (VALUES(2,'a'), (1,'b')) t(A,B) ORDER BY B desc` is perfectly valid, as I said. Notice how it uses `B` in the `ORDER BY` but the `B` column doesn't appear in the output? — Damien_The_Unbeliever, Jul 21 '22 at 07:51
Just forget you ever learned about "TOP (100) PERCENT" - it does nothing useful. — SMor, Jul 21 '22 at 11:42
Top 100 percent is required here in order to have the "order by" inside the subquery. Why the "order by" has to be there? See above. — Tunc Gercek, Jul 21 '22 at 12:13

mikkel · Accepted Answer · 2022-07-21T07:56:34.643

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

edited Jul 21 '22 at 07:56

answered Jul 21 '22 at 07:04

mikkel

473
3
10

You're right; Sign_In is indeed a date, so there's no reason for convert. "Convert" was used elsewhere in the query and I left it in the question here. Still, without the convert, I want it sorted in reverse order of "Date", but *without* that date in the output. I just want the 18 Jul, etc. in the output. – Tunc Gercek Jul 21 '22 at 07:24
Why "never use (nolock)". I almost always use with(nolock) because I don't want to lock tables unnecessarily just for reading. I'm happy with "dirty data", which is extremely unlikely in most cases for my queries. I learned this the hard way. – Tunc Gercek Jul 21 '22 at 07:25
`WITH(NOLOCK)` is a poorly named hint. It means "DON'T RESPECT OTHER LOCKS" - it is equivalent to READ UNCOMMITTED, and you rarely - if ever - need it. If you query is slow and thus locking things up, the correct course of action is to improve the slow query (query tuning / index optimizing). You may not be concerned about "dirty data" in your current position, but you may end up somewhere where guaranteeing that data is correct is a priority. – mikkel Jul 21 '22 at 07:36
1

Have a read of [Bad habits : Putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere), @TuncGercek . – Thom A Jul 21 '22 at 07:48
@TuncGercek Consider using Snapshot Isolation then. – Charlieface Jul 21 '22 at 12:32
Above solution from Damien does it! The only glitch was that my "Sign_in" is actually a datetime field instead of just date (which I had omitted to mention); therefore I had to use cast(Sign_in as date) in order to make sure the "GROUP BY" was able to group the people who signed in on the same **day**, not the same **millisecond** – Tunc Gercek Jul 21 '22 at 23:52

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

1 Answers1