CODEWITHSUNDEEP
mysql
Felipe Diesel
Felipe Diesel

Reputation: 71

Finding max in mysql when column is varchar

I have a varchar field that is code of letters and is auto incremented (manualy), so after A is B until after Z becomes AA and so on.

My issue is when I hit AA and I try to select MAX of this field:

SELECT MAX(letter) from jobs

Returns Z instead of AA. This is correct if you are ordering names, but I have a code. There's a way to get this right?

Upvotes: 7

Views: 2784

Answers (4)

paxdiablo
paxdiablo

Reputation: 881503

Since you're after the max of the longest field, I would start with something like the following. I'm not sure if the syntax is correct. Basically you have to ignore all but the longest column values.

select max(letter) from jobs
where len(letter) = (
    select max(len(letter)) from jobs
)

Whatever solution you choose, consider the option of adding another column to hold the letter length and have it controlled by an insert/update trigger (assuming MySQL has that).

By indexing that column, you can greatly accelerate your queries since you remove the per-row functions during select. The right time to incur the cost is when the data changes so that the cost of the calculation is amortised across all reads.

This won't work in situations where the database is written more often than read, but they're relatively rare in my experience.

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 753845

Do you have a maximum length for your field? If so, can you also stomach right-justifying the data in that field? The chances are that the answers are (1) empirically 'yes', and 5 might be an upper length, and (2) not really. However, were you able to say 'yes' to both, then you would be able to use this (where the boxes are blanks):

  • '☐☐☐☐A'
  • ...
  • '☐☐☐☐Z'
  • '☐☐☐AA'
  • '☐☐☐AB'
  • ...
  • '☐☐☐BA'
  • ...
  • '☐☐☐ZZ'
  • '☐☐AAA'

Realistically, that probably isn't going to be OK for you, and therefore it won't help.

Upvotes: 0

Kevin Stricker
Kevin Stricker

Reputation: 17388

Try this one. This will force it to weight longer strings as being higher than shorter strings:

SELECT letter
FROM jobs
ORDER BY LENGTH(letter) DESC, letter DESC
LIMIT 1;

Edit: Yup, forgot to sort DESC to get the MAX...

Upvotes: 9

jocull
jocull

Reputation: 21105

This may help? Sort them by length first, then by letter.

SELECT letter FROM jobs
ORDER BY LENGTH(letter) DESC, letter DESC
LIMIT 0, 1

Upvotes: 2

Related Questions