CODEWITHSUNDEEP
sqlsql-server
synaptikon
synaptikon

Reputation: 699

MS SQL Server MAX function

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?

Upvotes: 2

Views: 271

Answers (2)

Khan
Khan

Reputation: 18142

Your issue is your data type for distance. If it as VARCHAR or NVARCHAR it is sorting it alphabetically.

If you want it to sort numerically, you would want to use INT or something similar.

Upvotes: 5

Hart CO
Hart CO

Reputation: 34774

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.

Upvotes: 7

Related Questions