CODEWITHSUNDEEP
sqlsqlitemax
Jacob Kudria
Jacob Kudria

Reputation: 2300

sqlite3 - MAX() function does not return the maximum value of a column

Operating on a table of the following format:

name                      size        solution_length  solution_forks  backtravel_parallel  backtravel_perp  forks_to_dead  dead_overlap  weighted_dead_length
------------------------  ----------  ---------------  --------------  -------------------  ---------------  -------------  ------------  --------------------
AldousBroder_5_33342.txt  5           14               4               0                    0                4              26            444
AldousBroder_5_05916.txt  5           28               4               4                    0                4              28            728

This is how I would get the maximum value of a column.

sqlite> SELECT MAX(weighted_dead_length) FROM mazes_100k;

MAX(weighted_dead_length)
-------------------------
996

However, it doesn't seem to be the true maximum:

sqlite> SELECT COUNT(*) FROM mazes_100k WHERE weighted_dead_length = 1000;

COUNT(*)
----------
15

Why is it that if I try counting the number of rows with the value of a column higher than what was returned by MAX() I still get some hits? It seems as if MAX() doesn't return the true maximum of a column.

By trial an error, using COUNT(), it seems that the true maximum is 1004 - how can I get MAX() to return that?

Thanks!

Upvotes: 2

Views: 2159

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You are storing the value as a string rather than a number. Convert the value to a number for the max:

SELECT MAX(weighted_dead_length + 0)
FROM mazes_100k;

or:

SELECT MAX(CASt(weighted_dead_length as INTEGER) )
FROM mazes_100k;

Upvotes: 7

Related Questions