How to Select Max with String and Int value

Question

Hi I have this following table..

offer_number      training_title
**************
    ABC-1          SEMINAR
    ABC-9          SEMINAR
    ABC-10         SEMINAR
    ABCD-9         TRAINING
    EFGH-9         TESTING
    EFGH-10        TESTING

Mysql

SELECT *, MAX(offer_number) as offer_number_latest FROM (`training_program`) WHERE `training_title` LIKE '%SEMINAR%' GROUP BY `training_title` ORDER BY `offer_number` desc

I want to produce ABC-10 But I always get ABC-9 not ABC-10.

Sample 2:

 SELECT *, MAX(offer_number) as offer_number_latest FROM (`training_program`) WHERE `training_title` LIKE '%TESTING%' GROUP BY `training_title` ORDER BY `offer_number` desc

I need a result EFGH-10 but I always get EFGH-9 not EFGH-10.

9 > 1 - you would have to have 'ABC-09' for comparison to work that way.. Instead you can 'extract' the number from the string and use that - simplest should be "REPLACE(offer_number, 'ABC-', '')" to get only the numeric part of the string ([or using udf regex?](http://stackoverflow.com/a/986870/1786423) ) and then you will probably have to CAST() that to number (or add +0 to use automatic conversion) — jkavalik, May 25 '15 at 07:27

score 1 · Answer 1 · answered May 25 '15 at 07:34

If the offer_number prefixes are always the same per training_title, the following (nasty) query will work:

select training_title, 
  concat(
    left(offer_number, 
      locate('-', offer_number)
    ), 
    max(
      cast(substring(offer_number, locate('-', offer_number) + 1) as signed)
    )
  ) from offers group by training_title

demo here

score -1 · Answer 2 · answered May 25 '15 at 08:56

Mysql also have SUBSTRING function.

 mysql> select * from offer;
    +--------------+----------------+
    | offer_number | training_title |
    +--------------+----------------+
    | ABC-1        | SEMINAR        |
    | ABC-9        | SEMINAR        |
    | ABC-10       | SEMINAR        |
    | ABC-8        | SEMINAR        |
    | ABC-14       | SEMINAR        |
    | ABC-12       | SEMINAR        |
    +--------------+----------------+
    6 rows in set (0.00 sec)

    mysql> SELECT MAX(CAST(SUBSTRING(offer_number,LOCATE('-',offer_number)+1) AS SIGNED)) as MAX FROM offer ;
    +------+
    | MAX  |
    +------+
    |   14 |
    +------+
    1 row in set (0.00 sec)

    mysql> SELECT * FROM offer ORDER BY CAST(SUBSTRING(offer_number,LOCATE('-',offer_number)+1) AS SIGNED);
    +--------------+----------------+
    | offer_number | training_title |
    +--------------+----------------+
    | ABC-1        | SEMINAR        |
    | ABC-8        | SEMINAR        |
    | ABC-9        | SEMINAR        |
    | ABC-10       | SEMINAR        |
    | ABC-12       | SEMINAR        |
    | ABC-14       | SEMINAR        |
    +--------------+----------------+
    6 rows in set (0.00 sec)

How to Select Max with String and Int value

2 Answers2