0

I have the following values in my table : JC-1-1 , JC-1-2 , JC-1-3, JC-1-4,JC-1-5,JC-1-6,JC-1-7,JC-1-8,JC-1-9,JC-1-10,JC-1-11,JC-1-12,JC-1-15. When I try to select the value using mysql MAX function as below : 

SELECT MAX(job_card_no) AS max_job_card FROM `job_card`

I get the value returned as : JC-1-9 instead of JC-1-15 . How do I solve this issue?

H Dindi
  • 1,484
  • 6
  • 39
  • 68
  • 3
    max on a string, and not on a number. You should compare number, not string. JC-1-6 is greater or lower than JK-3-2? for exemple? SQL do string comparaison, but it's your logic to implement to really do what you want – A.H Dec 31 '15 at 11:23
  • Reason is mysql will compare based on lexical value. You may need to break your string and then compare.. – SMA Dec 31 '15 at 11:24
  • Check Jay given link – Alive to die - Anant Dec 31 '15 at 11:25

3 Answers3

2

Try this:

SELECT MAX(CONVERT(SUBSTRING_INDEX(job_card_no,'-',-1),UNSIGNED INTEGER)) AS max_job_card FROM `job_card`;

hope it will work :)

Manoj S Kadlag
  • 250
  • 2
  • 13
  • 4
    Why should the OP "try this"? A ***good answer*** will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. – Jay Blanchard Dec 31 '15 at 11:29
0

You can try SUBSTRING( string, start_position, [ length ] ) for getting result:

SELECT MAX(SUBSTRING(job_card_no, 6, 2)) AS max_job_card FROM `job_card`

SUBSTRING in mysql have three params.

  • your field name (your field name)

  • starting point (where you want to start)

  • length (how many character do you want)

devpro
  • 16,184
  • 3
  • 27
  • 38
0

This may not be the standard answer but give the desired output!!

You can't get proper output because query is not able to give maximum value for string. The solution is==> 1)do a query to get all job_card_no

SELECT job_card_no FROM `job_card`

2)Now,use implode() with foreach and then use max() function for that particular array

$arr = array('JC-1-1' ,  'JC-1-2' , 'JC-1-3', 'JC-1-4','JC-1-5','JC-1-6','JC-1-7','JC-1-8','JC-1-9','JC-1-10','JC-1-11','JC-1-12','JC-1-15');
Kunal
  • 604
  • 10
  • 18