mySQL max() not returning max result

Question

I have trouble trying to retrieve max courseid

Data:

coursename    courseid    
----------    --------
0001_Course   JAS9997
0002_Course   JAS9998
0003_Course   JAS9999
0004_Course   JAS10000

Query:

SELECT max(courseid) FROM tblcourse WHERE courseid LIKE '%JAS%'

The LIKE is to narrow down to courseid that begin with JAS.

The query only return JAS9999 as the max result but the max courseid is JAS10000. Am I missing something?

Answer 1

It can't do MAX on numbers embedded in text like this. It makes alphabetical ordering and so JAS9 goes after JAS1. You will have to do max on a substring:

MAX(CAST(SUBSTRING(courseid FROM 4) AS UNSIGNED))

Answer 2

A lot of pure sql solutions where provided that should work on the assumption that all of the courses are formated with a three character prefix followed by numbers. I though I would throw in with a php solution.

First get all of the courses that match your like clause in an array.

 $matching = array();
 while ($matching[] = $query->fetchNext()){}

Then

natsort($matching);
$last = end($matching);

Last will contain the last JAS10000 in your case