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?

Yes it was string. Using CAST now for INT sorting. Ty so much! — synaptikon
– synaptikon, Commented Jul 18, 2013 at 20:21

Hart CO · Accepted Answer · 2013-07-18 19:21:48Z

7

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.

answered Jul 18, 2013 at 19:21

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yeah, that is what I was getting at with my question.

+1 . . . I guessing this is right. But technically, it would be that the int is not numeric (or is a character string).

exactly..that was it...This lead to an important clarification about concepts. +1 all!

Khan · Accepted Answer · 2013-07-18 19:22:59Z

5

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.

answered Jul 18, 2013 at 19:22

Khan

18.2k5 gold badges52 silver badges60 bronze badges

+1 Thanks guys. That fixed it. Indeed that was a key point. Added to my sql notes. :)