Absolute ordering by data returned from DB

Question

In PHP I am making a query to my database using Mysql. In my database I have the table fotos_album and inside I have the name of the photo. I want to sort the table by field nome.

We imagine that the table has the following data:

id         nome
1          corrida (1 de 140)
2          corrida (20 de 140)
3          corrida (5 de 140)
4          corrida (2 de 140)

The purpose is that my ordination be as follows::

id         nome
1          corrida (1 de 140)
4          corrida (2 de 140)
3          corrida (5 de 140)
2          corrida (20 de 140)

It is not enough to use ORDER BY nome ASC, then how can I make the ordination correct?

Edited: The name is entered by the user. If he enters the name field as I wrote above, a simple sort does not work

I tried the following, but it doesn’t work properly in all cases:

SELECT *
FROM fotos_album
WHERE id = '$id_noticia'
ORDER BY 
  SUBSTR(nome,
     LEAST(
       CASE WHEN LOCATE('0', nome)>0 THEN LOCATE('0', nome) ELSE LENGTH(nome) END,
       CASE WHEN LOCATE('1', nome)>0 THEN LOCATE('1', nome) ELSE LENGTH(nome) END,
       CASE WHEN LOCATE('2', nome)>0 THEN LOCATE('2', nome) ELSE LENGTH(nome) END,
       CASE WHEN LOCATE('3', nome)>0 THEN LOCATE('3', nome) ELSE LENGTH(nome) END,
       CASE WHEN LOCATE('4', nome)>0 THEN LOCATE('4', nome) ELSE LENGTH(nome) END,
       CASE WHEN LOCATE('5', nome)>0 THEN LOCATE('5', nome) ELSE LENGTH(nome) END,
       CASE WHEN LOCATE('6', nome)>0 THEN LOCATE('6', nome) ELSE LENGTH(nome) END,
       CASE WHEN LOCATE('7', nome)>0 THEN LOCATE('7', nome) ELSE LENGTH(nome) END,
       CASE WHEN LOCATE('8', nome)>0 THEN LOCATE('8', nome) ELSE LENGTH(nome) END,
       CASE WHEN LOCATE('9', nome)>0 THEN LOCATE('9', nome) ELSE LENGTH(nome) END
     )
   ) + 0

Answer 1

Assuming nome is an open textual field, what seems to be bothering your client is that lexicographic ordering does not take into account the interpretation a human would give for the string. While the computer sees only a sequence of characters (at most, taking into account the collation of his 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 that the computer will be able to recognize this structure and give its elements a natural order (i.e. if the collection name and the total are fixed, the indices must be in ascending order). It turns out that the computer doesn’t see things that way. For him the string corrida(20 de 140) is no different from corrida(9001 de -42) which is no different from alllll234hlkhssdf.

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

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

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

1. Break your string into pieces, where each piece is a number (\d+) or not (\D+);
2. Interpret numbers as numbers (i.e. not text), and sort them as such;
3. What is not number, order lexicographically, respecting the collation of your language.

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

Probably this will be too complex to do only via SQL, being better accomplished in your application layer or maybe as a stored Procedure.

Updating: I can’t suggest anything other than what was proposed above, your new solution attempt fails because it ignores the beginning of the string (ex.: b1 would appear before a2) and also because yet compares as string - not as number (unless that + 0 at the end, who did not understand what it does, is in fact making a cast implicit to 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 types, not just a specific collection. Whatever solution is adopted, 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 be 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 it were his fallback for the length, will also be before arvore).
• If you use my suggestion above, the order will look like in the proposed list. But note that the customer can still claim that corrida(5 de 140) is among corrida(5 de 20) and corrida(20 de 20)...
  • And the attempt to solve this case will bring more complications (ex.: corrida N/M will get the wrong order). You can’t do magic, the client has to understand that the computer has no intelligence to always order as he wants.

etc. Pay attention to this, for it is of no use that you focus too much on this specific requirement now, if later your ordering turns completely crazy, when your system is in practice in a real case.

Answer 2

Try using the following code:

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