CODEWITHSUNDEEP
mysqlmax
muhammad aditya
muhammad aditya

Reputation: 63

mysql select MAX but display not as expected

i have a query looking for max value, but display not as expected

this is my data in table enter image description here

i just to get max value in this column. i use query "SELECT max(substr(no_box,3)) as maxnb FROMtbl_pengecekan" but display value is 9

enter image description here

the expected value is 10

please help me

Upvotes: 0

Views: 285

Answers (3)

Ullas
Ullas

Reputation: 11556

You can also do take the max value by removing the A- from the string.

Query

select max(`t`.`num`) as `maxnb` from (
    select cast(replace(`no_box`, 'A-', '') as signed) as `num`
   from `tbl_pengecekan`
) as `t`;

An other way around is, order the column first by descending order of the length and then descending order of the column itself. And then limit the result to 1.

Query

select replace(`no_box`, 'A-', '') as `maxnb`
from `tbl_pengecekan`
order by length(`no_box`) desc, `no_box` desc limit 1;

Find a demo here

Upvotes: 2

Johan
Johan

Reputation: 3611

You're getting the value 9 because that's the lexigraphical maximum value in the set your querying on. If you want to get the numerical maximum value you must first cast the values to integers (basically convert from string to int). You can do this with cast(substr(no_box, 3) as signed)

Full code

SELECT 
    max(cast(substr(no_box, 3) as signed)) as maxnb 
FROM tbl_pengecekan

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You need to cast substr(no_box,3) to int then apply max

demo

select max(cast(substr(no_box,3) as signed)) from tablename

Upvotes: 5

Related Questions