CODEWITHSUNDEEP
mysqlsorting
kylex
kylex

Reputation: 14406

MySQL, sort single letters first, then double letters, then triple letters

Given a set of letters {a,aa,b,bb,c,cc,bbb}

I would like to order them FIRST by single letters, then double letters, then triple letters. Within that construct I'd like them to be in alphabetical order.

Example: a,b,c,aa,bb,cc,bbb

For my use case, it will only ever go up to 3 letters. Any ideas?

Upvotes: 0

Views: 273

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

For the data you have provided, you can just use the length:

order by length(letters),
         letters;

This assumes that the letters are as described in the question, with no additional letters afterwards.

If these are prefixes, the brute force approach would be:

order by ((substring(letters, 1, 1) = substring(letters, 2, 1)) +
          (substring(letters, 1, 1) = substring(letters, 3, 1)
         ), letters;

That is, compare the initial character to the next two. The above returns 0, when the first and second characters are different; 1, when the first and second are the same but the third different; and 2 for three character prefixes.

Upvotes: 2

Related Questions