MySQL BETWEEN Two Negative Numbers Returning No Results

When recently working on an application that contained a map, I wanted to get all items from a MySQL database between two latitude/longitude coordinates.

As a result my initial queries looked similar to the below:

1. SELECT `fieldNames`  
2. FROM `my_table`   
3. WHERE   
4.     `latitude` BETWEEN 42.689506 AND 43.989992   
5. AND   
6.     `longitude` BETWEEN -71.922720 AND -73.122480  

Looks ok right? I knew there were definitely entries in the database that fell between these coordindates, so why weren’t they coming back?

The Solution

The problem is the order in which the negative numbers have been placed. When working with negative numbers, the query requires that you put the smallest of the two numbers (the numerically larger of the negative values) first.

1. SELECT `fieldNames`  
2. FROM `my_table`   
3. WHERE   
4.     `latitude` BETWEEN 42.689506 AND 43.989992   
5. AND   
6.     `longitude` BETWEEN -73.122480 AND -71.922720   

A quick swap around and the results I were expecting started to appear.