CODEWITHSUNDEEP
phpsqlselectnumbersmax
Martin
Martin

Reputation: 75

SQL Finding MAX number in a VARCHAR typed column

I have got SQL table where I have column DISTANCE (varchar50). It's number with dot.

+----+--------+--------+----------+
| ID | USERID | MONTH  | DISTANCE |
+----+--------+--------+----------+
| 1  |    1   | 201707 |   7.25   |
+----+--------+--------+----------+
| 2  |    2   | 201707 |   9.17   |
+----+--------+--------+----------+
| 3  |    2   | 201707 |  10.31   |
+----+--------+--------+----------+
| 4  |    1   | 201706 |  10.08   |
+----+--------+--------+----------+

I would like to display MAX value od DISTANCE. If I ORDER BY distance DESC the result is:

SELECT * FROM mytable ORDER BY distance DESC
9.17
7.25
10.31
10.08

I try to find the MAX but still is not correct

SELECT MAX(distance) AS mvzd FROM mytable
9.17

How I can have correct max distance value which should be 10.31 with the provided sample data ?

Upvotes: 2

Views: 491

Answers (2)

ino
ino

Reputation: 2581

The problem is that you use wrong data type of the DISTANCE. In your case you store the distance values as varchar and that's why the ORDER BY and MAX() functions does not work as expected - in this case the ORDER BY is sorting values as a string from the very first character. In your example you tried to sorted in descending order to be like from lowest to larger - this will give you "unexpected" results if there will be value like 98.76 or 111.15.

All you need is to set the data type of DISTANCE column to float - a small number with a floating decimal point.

Change settings of your table to use float: 

ALTER TABLE `mytable`
  CHANGE COLUMN `distance` `distance` 
  FLOAT NULL DEFAULT NULL AFTER `month`;

Data stored as float will support all native functions such as MIN, MAX, ORDER BY etc. which will work as expected without any workarounds.

Here is corrected example using float: http://sqlfiddle.com/#!9/1f8692/1
Here is extended example using incorrect varchar(50): http://sqlfiddle.com/#!9/4da754e/1

Upvotes: 0

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

PROBLEM

SOLUTION

You can use the following query :

select max(cast(distance as decimal(10,2))) AS mvzd FROM mytable

By your existing query, you are finding MAX on a VARCHAR typed column and you are getting correct result what you wrote! If you want to have your desired result, first you have to convert the type of that column like i did in the above query.

Upvotes: 2

Related Questions