CODEWITHSUNDEEP
sqlsql-order-by
gustyaquino
gustyaquino

Reputation: 767

SQL ordering by text ASC, show numbers at last position

I have a SQL query and I need to order the result in ASC, but I need the numbers and spaces to be in the last position of the result, not first.

SELECT title FROM movie ORDER BY title ASC

Using the code above I get the titles starting from [space], 0-9, A,B,C... I need this form: A,B,C,...,Z,[anything else]

Thanks for any help.

Upvotes: 5

Views: 3712

Answers (2)

Ben Hoffstein
Ben Hoffstein

Reputation: 103395

This is a bit of a hack, but it should work.

SELECT title FROM movie 
ORDER BY CASE WHEN title LIKE '[0-9]%' THEN 'ZZZ' + title ELSE title END ASC

The idea is to make your order clause transform the items starting with numbers by prepending them with ZZZ. As for the items starting with a space, you may just want to clean them up with LTRIM.

Upvotes: 1

zerkms
zerkms

Reputation: 255155

ORDER BY CASE
           WHEN LOWER(LEFT(title, 1)) BETWEEN 'a' AND 'z' THEN 0
           ELSE 1
         END,
         title

Upvotes: 6

Related Questions