Finding Maximum of a column in My Sql

Question

This is the Materials Table

ID DCNumber  InvoiceNumber  DeviceModel                          DeviceSerialNumber
 1 NULL               1001  Audio Door Phone                      1
 2 NULL               1001  Audio Door Phone                      2
 3 NULL               1001  Audio Door Phone                      3
 4 NULL               1001  Audio Door Phone                      4
 5 NULL               1001  Audio Door Phone                      5
 6 NULL               1001  Audio Door Phone                      6
 7 NULL               1001  Audio Door Phone                      7
 8 NULL               1001  Audio Door Phone                      8
 9 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   1
10 NULL               1001  Forgo cleaning kit,HDP-5000.(89200)   2
11 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   3
12 NULL               1001  Forgo cleaning kit HDP-5000.(89200)   4
13 NULL               1001  Forgo cleaning ldt.HDP-5000.(89200)   5
14 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   6
15 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   7
16 NULL               1001  Forgo cleaning kit,HDP-5000.(89200)   8
17 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   9
18 NULL               1001  Forgo cleaning kit,HDP-5000.(89200)  10
19 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)  11
20 NULL               1001  Forgo cleaning kit HDP-5000.(89200)  12
21 NULL               1001  IR Bullet Camera TC62L2A              1

with ID as primarykey and i am trying to execute following query..

select DeviceModel,group_concat(`DeviceSerialNumber` separator ',') ``as`SerialNumbers`,max(DeviceSerialNumber) as 'Quantity' from materials  group by InvoiceNumber,DeviceModel having InvoiceNumber= '1001';

and following was the output

but Quality Column return wrong data ..i need maximum of deviceserialnumber as Quality for a particular device. ie in this case 8,12,20,40

Answer 1

As pointed out by Strawberry in a comment the issue is most likely that the DevinceSerialNumber column is a of character type, which would explain why 9 is higher than 12. A solution is to cast the column to an integer:

select
  DeviceModel,
  group_concat(`DeviceSerialNumber`  separator ',') 
as "SerialNumbers",
  max(cast(DeviceSerialNumber as unsigned)) as "Quantity" 
from materials  
where InvoiceNumber= '1001'
group by InvoiceNumber, DeviceModel;

See this sample SQL Fiddle in which the first query gives the incorrect result and the second (using a cast) gives the correct result.

To get the DeviceSerialNumber in the group_concat in order you should also add an order by clause to the function:

group_concat(
  cast(DeviceSerialNumber as unsigned) 
  order by cast(DeviceSerialNumber as unsigned) 
  separator ','
) 

And finally, to avoid having to use cast in multiple places you could do the cast in a derived table:

select
  DeviceModel,
  group_concat(DeviceSerialNumber order by DeviceSerialNumber separator ',') 
as "SerialNumbers",
  max(DeviceSerialNumber) as "Quantity" 
from (
  select 
    DeviceModel, 
    InvoiceNumber, 
    cast(DeviceSerialNumber as unsigned) DeviceSerialNumber
  from materials
) materials
where InvoiceNumber= '1001'
group by InvoiceNumber, DeviceModel;