sort order by desc not working as expected

Question

I have this query in SQL

SELECT Book FROM Library ORDER BY Book desc 

My desired sort

[Expired]Web Publishing Co., Ltd.
[Expired]sBox Co.,Ltd
[Expired]mTertainment
wwww

but result showed as follows

wwww
[Expired]sBox Co.,Ltd
[Expired]mTertainment
[Expired]Web Publishing Co., Ltd.

Answer 1

I see two issues here with your ordering. First is that the bracket symbol is lexigraphically less than alphanumeric characters, which is not in agreement with what you want. Second is that you have a case sensitivity issue with your book titles. This is resulting in sBox appearing greater lexigraphically than Web, even though the former letter appears earlier in the alphabet from a case insensitive point of view.

One way to get the ordering you want is to use two conditions. The first condition separates book titles beginning with alphanumeric titles from those beginning with symbols like brackets. The second condition uses a case insensitive ordering by the actual book title.

SELECT Book
FROM Library
ORDER BY CASE WHEN REGEXP_LIKE(SUBSTR(Book, 1, 1), '[^A-Za-z0-9]') THEN 0 ELSE 1 END,
         LOWER(Book) DESC

Actually, perhaps a more exact way to handle this would be to check if the book title begins with any bracketed label, i.e. ^[.*]:

ORDER BY CASE WHEN REGEXP_LIKE(Book, '^[[].*[]]') THEN 0 ELSE 1 END,
         LOWER(Book) DESC