CODEWITHSUNDEEP
mysql
Jamie
Jamie

Reputation: 433

mySQL max() not returning max result

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?

Upvotes: 5

Views: 351

Answers (3)

Orangepill
Orangepill

Reputation: 24645

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

Upvotes: 1

mirkobrankovic
mirkobrankovic

Reputation: 2347

Use:

SELECT MAX(CAST(SUBSTRING(courseid,4) AS UNSIGNED)) 
FROM tblcourse 
WHERE courseid LIKE '%JAS%'

Upvotes: 0

sashkello
sashkello

Reputation: 17871

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))

Upvotes: 6

Related Questions