CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-order-by
Bell Aimsaard
Bell Aimsaard

Reputation: 505

sort order by desc not working as expected

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.

Upvotes: 4

Views: 1135

Answers (2)

Marcos Alexandre Sedrez
Marcos Alexandre Sedrez

Reputation: 537

Do you need select the "[Expired]"? If not, maybe the solution is to replace it:

SELECT REPLACE('[Expired]sBox Co.,Ltd', '[Expired]', '') as book 
FROM dual UNION
SELECT REPLACE('[Expired]mTertainmentas', '[Expired]', '') as book 
FROM dual UNION
SELECT 'www' as book FROM dual
ORDER BY book DESC

Or like @Tim did show, with REGEX:

SELECT REGEXP_REPLACE('[xxxa]sBox Co.,Ltd', '^[[].*[]]', '') as book 
FROM dual UNION 
SELECT REGEXP_REPLACE('[xxxb]mTertainment', '^[[].*[]]', '') as book 
FROM dual UNION 
SELECT 'www' as book FROM dual 
ORDER BY book DESC

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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

Upvotes: 7

Related Questions