I have a table like:
------------------ | ID | Animal | ------------------ | 1 | B1 | ------------------ | 2 | B2 | ------------------ | 3 | B3 | ------------------ | 4 | B4 | ------------------ | 5 | B5 | ------------------ | 6 | 1 | ------------------ | 7 | 2 | ------------------ | 8 | R71 | ------------------ | 9 | R72 | ------------------ | 10 | 4 | ------------------ | 11 | 7 | ------------------ | 12 | R1 | ------------------ | 13 | 77 | ------------------ | 14 | 3 | ------------------ | 15 | 5 | ------------------ | 16 | R2 | ------------------ | 17 | T3 | ------------------ | 18 | C50 | ------------------ | 19 | R5 | ------------------ | 20 | R6 | ------------------
My DB is shared hosted where I don’t have any possibility to set new configurations and, until last week, my query with final ORDER BY animal + 0 ASC was returning the result orderly, like: B1…B5, R1…R72, 1…700. Yesterday our database returned all results, with the same query, disorderly while, in developer DB, it’s returned orderly as always.
I consult the host service, but it affirms there’s no problem with DB and it doesn’t have new configuration.
I tried to use CAST() and CONVERT() function, but it returned animal disorderly.
Can someone shed light on this issue?
Advertisement
Answer
For this sample data this will work:
SELECT *
FROM tablename
ORDER BY Animal + 0,
CASE WHEN Animal + 0 = 0 THEN LEFT(Animal, 1) END,
CASE WHEN Animal + 0 = 0 THEN SUBSTR(Animal, 2) + 0 END
See the demo.
Results:
> ID | Animal > -: | :----- > 1 | B1 > 2 | B2 > 3 | B3 > 4 | B4 > 5 | B5 > 18 | C50 > 12 | R1 > 16 | R2 > 19 | R5 > 20 | R6 > 8 | R71 > 9 | R72 > 17 | T3 > 6 | 1 > 7 | 2 > 14 | 3 > 10 | 4 > 15 | 5 > 11 | 7 > 13 | 77