I have a table with a column named price typed as
And there is the problem. You have a column that obviously has numeric values (assuming the name
price is relevant) and you have chosen a char type.
I want the maximum value of this column. I am using this query:
SELECT MAX(price) FROM product_management.`item_list` ;
but the result is wrong.
The result is 100% correct but not what you expected. The database did exactly what you asked it to - but not what you though you were asking.
You asked for the
MAX() of a
varchar column and that's what you got. Problem is that for char columns, the ordering is done lexicographically - as in dictionaries - and not with numeric order. Because char type columns have char values (strings) and not numbers.
So, for the database, they are just strings of characters. You could very have a value of
peters-sellers in there. The ordering of values of a char column would be - even if all the values are visually "numbers".
And of course your query would return the lexicographic maximum (
997) and not the numeric maximum (
The best solution would be to convert the column to a numeric type -
decimal, depending on the values you expect to have - and then
ORDER BY would work as you expect.
There are workarounds, if you can't change the type (by casting the value to a numeric type before applying MAX/MIN functions) but I don't recommend it. This would still allow to store nonsense data in the column and possibly get conversion errors (which number should
'peter-sellers' be converted to?)