MAX of a varchar column

I have a table with a column named price typed as varchar. 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. Please solve this problem.

I am using [SB] SQLyog Ultimate Ver. 9.0.2.0.

I have a table with a column named price typed as varchar.

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".

1
11
165
17
2
20
200
21
21789
3
35
350
9
99
997

And of course your query would return the lexicographic maximum (997) and not the numeric maximum (21789).

The best solution would be to convert the column to a numeric type - integer, decimal, depending on the values you expect to have - and then MAX() and MIN() and 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?)

StackExchange Database Administrators Q#135283, answer score: 6