MySQL Order By Numeric Difference
A nice way I found to use MySQL to order a result set by the absolute value of the numeric difference between two numbers, where one is the value in a column. I had never used SQL to subtract two numbers, let alone take the absolute value and use that to order the results. You could use something like this to show numbers that are most closely related, maybe in a zipcode application or a priority scoring application.
SELECT * FROM `table`
WHERE `something`=`whatever`
ORDER BY ABS ( 711 - `num` )
WHERE `something`=`whatever`
ORDER BY ABS ( 711 - `num` )
Home | MySQL | MySQL Order By Numeric Difference
You’re currently reading “ MySQL Order By Numeric Difference ,” an entry on BRADINO
- Published:
- 11.9.07 / 6pm
- Category:
- MySQL
This article saved my skin today, thanks for sharing!
I was forced to use a slightly different method though due to the nature of the query I was working on (see below for simplified version):
SELECT Price, ABS( 711 - `Price`) AS PriceDifference
FROM `Product`
ORDER BY PriceDifference
I noticed doing it this way that if price was an unsigned integer and it was of a higher value than the number it was being compare to (in this case 711), it returned an incorrect result.
I understand why this is happening (looping back through to highest possible value as unsigned fields can’t be negative) but I’ve yet to figure out the best way to get around this? (Other than altering the field so it’s no longer unsigned)
Anyway, just thought I’d share this in case anyone else came across the same problem (took me a wee while to figure out!)
Cheers, keep up the good work!