查找两个varchar字段的最大数值

Question

I'm trying to find the max value contained in two separate fields of my table.

The code I'm using in my model is:

$query = $this->db->query("select max($field1) as max_id_1 from default_table1");
$row1 = $query->row_array();
$query = $this->db->query("select max($field2) as max_id_2 from default_table1");
$row2 = $query->row_array();
return max($row1['max_id_1'], $row2['max_id_2']);

I'm a complete novice where PHP and CodeIgniter is concerned - as I'm sure my code demonstrates :)

It is working insofar as it's returning values, but not the maximum values I have in the fields. For instance I know there is a 4000 value but the highest returned is 750.

I'm wondering if this is because the fields are of type VARCHAR because although they predominantly contain numbers there are some that contain characters (- or &) or the word 'to' so I couldn't use the INT type. Because of using VARCHAR is it failing to see that 4000 is larger than 750?

If so is there a way to cast the field contents as integer before checking for the max value, and will this be affected by the non-integer values in the fields?

All offers of help and advice is gratefully received.

Tony.

Answer 1

This can be done using SQL using MySQL's implicit type conversion:

select max(case when (field1+0)>(field2+0) then field1+0 else field2+0 end)
from default_table1

Using +0 would convert varchar to number and also ignore any characters that follow after the number. If you still need the original content, you can write the query like this:

select case when (field1+0)>(field2+0) then field1 else field2 end
from default_table1
order by case when (field1+0)>(field2+0) then field1+0 else field2+0 end desc
limit 1