Select the largest number from MYSQL Table with added Prefix

Question

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 1

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

Answer 2

SELECT LEFT(customer_id,2) AS PREFIX,
       MAX(RIGHT(customer_id,4)) AS MAX
FROM table1
GROUP BY LEFT(customer_id,2)

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Table1
    (`customer_id` varchar(6))
;

INSERT INTO Table1
    (`customer_id`)
VALUES
    ('DD1234'),
    ('DD1222'),
    ('EE2345'),
    ('EE6789')
;

Query 1:

SELECT LEFT(customer_id,2) AS PREFIX,
       MAX(RIGHT(customer_id,4)) AS MAX
FROM table1
GROUP BY LEFT(customer_id,2)

Results:

| PREFIX |  MAX |
|--------|------|
|     DD | 1234 |
|     EE | 6789 |

EDIT :

SELECT MAX(RIGHT(customer_id,4)) AS MAX
FROM table1
GROUP BY LEFT(customer_id,2)
WHERE LEFT(customer_id,2) = 'HE'

Answer 3

USE:

SELECT MAX(RIGHT(customer_id,4)) AS max fROM table_name 
    WHERE LEFT(customer_id,2)='HE';