3

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.

Improve this question
0

2 Answers 2

Reset to default
6

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?)

Improve this answer
2
  • 2
    To avoid errors you could use something like ORDER BY CASE WHEN price REGEXP '^[[:digit:]]+$' THEN price ELSE 0 END to guard against non-numeric values. But as you say, fixing the datatype is by far the best solution for this and other reasons. Ordering by a computed value like this will almost certainly force a "sort in file" if one isn't already happening so for large data it is not going to be efficient.
    – David Spillett
    Apr 15, 2016 at 8:46
  • 1
    @DavidSpillett thnx, I agree. Then we have 2 problems (Regex joke ;) That's why I didn't want to add more details towards the conversion solutions, because they get ugly and complicated (regex, computed columns, then indexes on computed columns, etc...), while the simple change of type eliminates all that.
    – ypercubeᵀᴹ
    Apr 15, 2016 at 9:24
0

We can use the Convert function. For more info on it, see

https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_convert

An example:

SELECT max(Convert(`price`, SIGNED)) FROM product_management.`item_list`;
Improve this answer
1
  • Yes, you could - if you have no other option (change the field's Data Type) and as long as you don't mind waiting for the database to read each and every row in the table, feed the field value through the convert function and then find the largest of each of those results. Using functions on fields will [almost always] result in this Table Scanning behaviour, which is appallingly slow on large tables. You should always aim to store data in fields of the correct Data Type to avoid problems like this.
    – Phill W.
    Apr 4, 2019 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.