SQL Server 2008 MAX and MIN function not working as expected with negative numbers

Question

I've noticed that the MAX/MIN aggregate functions for SQL Server 2008 do not work as expected with negative numbers.

I was working with latitude and longitude values (many are negative #s) and I'm getting results that appear to only be looking at the absolute value.

SELECT 
    MAX(g.Geo_Lat) AS MaxLat, MAX(g.Geo_Long) AS MaxLong,
    MIN(g.Geo_Lat) AS MinLat, MIN(g.Geo_Long) AS MinLong  
FROM Geolocations g

Here are results of a query:

MaxLat          MaxLong         MinLat        MinLong
38.3346412      -85.7667496     38.1579234    -85.5289429

note the results for maxlong and minlong are incorrect.

Is there some workaround for this (other than a special UDF)?

Answer 1

Data types and collation determine order.

Geographic data, stored for instance as a geography type, could sort differently than float values - but in this case, they would not. Gopegraphic types are not sortable, only the latitudes and longitudes are, as you display. But those output as float values.

What data type are you using that causes this to occur? Affter some testing, I eventually figured it out. It would work as expected for geographic data, or any numeric type that holds negative numbers.

You are storing your latitudes and longitudes as text data - aren't you?

Cast them as floats. That will fix it.