Absolute ordering for data returned from DB

Try using the following code:

SELECT nome FROM fotos_album ORDER BY LENGTH(nome), nome

Assuming that nome is an open textual field, what seems to be troubling your client is that lexicographical ordering does not take into account the interpretation a human would give to the string. While the computer sees only one sequence of characters (at most, taking into account the collation of its language), a human sees a series of words, signs, numbers, which for him follows a logical structure:

"corrida(20 de 140)" ::== Nome de uma coleção, "(", índice, " de ", total, ")"

And he hopes the computer will be able to recognize this structure and give its elements a natural order (i.e. if the collection name and total are fixed, the indexes must be in ascending order). It turns out the computer does not see things that way. For it the string corrida(20 de 140) is not different from corrida(9001 de -42) which is not different from alllll234hlkhssdf .

A person with a minimum of understanding of this would simply shrug and rename your photos to:

corrida(001 de 140)
corrida(020 de 140)
corrida(005 de 140)
corrida(002 de 140)

But if the customer insists on this, and you want to please him at all costs, I would suggest the following:

(Clarifying: represent your name as a list ["corrida(", 1, " de ", 140, ")"] and sort by the first element, then by the second, and so on)

This will probably be too complex to do only via SQL, being best performed at your application layer or perhaps as a stored procedure .

Update: I can not suggest anything other than what was proposed above, your new solution attempt fails because it ignores the beginning of the string (eg, b1 would appear before a2 ) and also because still compares as string - not as number (unless that + 0 at the end, I did not understand what it does, actually doing a cast implied for number).

However, I can suggest a mass of data for testing - because it is important to remember that not every file in your database will have the same format, or even belong to the same user; a table stores data of all kinds, not just from a specific collection. Whatever solution you choose, make sure it works in the following cases:

arvore
árvore
arvore(40 de 50)
cor(60 de 140)
corrida 20/5
corrida 20/20
corrida 140/5
corrida 140/20
corrida(5 de 20)
corrida(5 de 140)
corrida(20 de 20)
corrida(20 de 140)
palavramuitogrande(1 de 1)

Since:

• If your collation is wrong, árvore will be after palavramuitogrande ;
• If you sort by length first and then by content, cor(60 de 140) will be before arvore(40 de 50) ;
• If you sort by the first number found, palavramuitogrande will be before cor (if your fallback is the length, it will also be before arvore ).
• If you use my suggestion above, the order will look like the proposed list. But note that the client can still claim that corrida(5 de 140) is between corrida(5 de 20) and corrida(20 de 20) ...
  • And attempting to resolve this case will bring more complications (eg, corrida N/M will get the wrong order). Can not do magic, the client has to understand that the computer does not have the intelligence to always order as he wants.

Etc. Pay attention to this, because it's no good if you focus too much on that specific requirement now, if your ordering turns out to be completely crazy then when your system is in real-life practice.