Select max is returning a wrong value in MySQL

Question

enter image description here

I have above table and I want to get the highest value from table bids where bid_id=60 using the following query

SELECT MAX(offer_amount) as maz FROM bids WHERE bid_id = 60

The problem is I'm getting the result as 80 in instead of the correct value which is 7000000

Anybody with an idea of how to solve this?

• 1000111: Aberrant case. Would you please share an sql fiddle ?
• Strawberry: It's "correct". And store integers as integers and strings as strings
• Nicholas Macharia: @Strawberry it have worked I have changed the amount_column from VARCHAR to INT
• Strawberry: Well, of course;-)
• Shushil Bohara: You can get desired output by using CAST but what about if you perform SUM, AVG function in that column again you have to use CAST where ever you need and it's not correct way.

Answer 1

Store offer_amount in a numeric field (such as integer or decimal), not as text. Quick solution is to use the CAST() function in the query to cast the field's data type to a numeric one.

• Jonathan Leffler: And we can tell it's a string, not a number, because the values are left-justified in the displayed fragment of the table.
• Strawberry: Quick solution is to add 0 to the value
• Nicholas Macharia: Changing offer_amount from VARCHAR to INT have worked. Thank you guys