user1011713 user1011713 - 6 months ago 23
MySQL Question

Select the largest number from MYSQL Table with added Prefix

I have a table that unfortunately, I can't alter in any way and have to work with what I have.

The mysql table has a field labeled, "customer_id". It has 2 prefixed letters with a 4-value numerical number.

EX:
BI8392

HE8492

WO1293

How can I select the largest numerical value with a certain prefix? For example, assume that I wanted to select the largest number with a prefix of HE. How can I select that value?

Any help is absolutely appreciated. I've been stuck for a while now.

Answer

Since all values left padded you can do

SELECT RIGHT(MAX(customer_id), 4) max_val
  FROM table1
 WHERE customer_id LIKE 'HE%'

Make sure that you have an index on customer_id which you probably do based on a name of the column. LIKE will most likely use it.

Here is SQLFiddle demo