MS SQL Server MAX function

Question

I am a bit new to MS SQL Server and I am trying to execute a really simple query that goes like:

SELECT name, id, description, distance 
FROM my_table
WHERE id IS NOT NULL
ORDER BY distance DESC

Where my distance values range from 1 to 18752.

For some reason, the above query gives me the top-most distance value as 9999 whereas the values greater than 9999 are found somewhere below.

I also tried getting

MAX(distance)

which still gives me 9999.

Is there some key aspect of using this function I am missing out?

Answer 1

Your distance is not an int, I presume:

SELECT name, id, description,distance
FROM my_table
WHERE id IS NOT NULL
ORDER BY CAST(distance AS INT) DESC

CAST as INT it will sort by the integer value.