user1011713 user1011713 - 1 year ago 82
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.




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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download