CODEWITHSUNDEEP
sqlsql-order-bycase
OldCurmudgeon
OldCurmudgeon

Reputation: 65821

Using CASE in ORDER BY to push some records to the end

I commonly use a construct somewhat like:

ORDER BY CASE WHEN Location = 'ROOT' THEN '~' ELSE Location END

In an ASCII environment this successfully pushes all records with a Location of ROOT down to the end while all others are sorted normally. This works because the '~' character is close to the end of the character set. However, with the more ubiquitous use of databases with wider characters sets than ASCII I expect this technique will occasionally not work.

What alternative is there? Specifically I want a technique that will push records with a specific value in a field to the end of the list.

I am working primarily with MS SQL but I would also be interested in a general technique and one that will work with Oracle.

Upvotes: 3

Views: 305

Answers (2)

podiluska
podiluska

Reputation: 51494

ORDER BY CASE WHEN Location = 'ROOT' THEN 1 ELSE 0 END, Location

Upvotes: 3

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79959

To push the values with Location = 'ROOT' to the end try this:

ORDER BY CASE WHEN Location = 'ROOT' THEN 1 ELSE 0 END, Location

Upvotes: 5

Related Questions