Closest Match

[NancyJ]

Is it possible in SQL to find a 'closest match' with a set of numbers? eg
I have a db with fields R, G & B
then I have values r, g & b and I want to find the record that is closest in value to r,g&b.

[GJay]

it's a bit late for my head to be thinking properly, but the first thing that comes to mind is as follows: (typed from my head, so expect typos...)
to get the closest to (255,100,0)..
SELECT (r-255) AS rdiff, (g-100) as gdiff, (b-0) as bdiff FROM table ORDER BY rdiff, gdiff, bdiff

Or, and I'm not sure if this will work...might need some code involved
SELECT (r-255) AS rdiff, (g-100) as gdiff, (b-0) as bdiff, rdiff+bdiff+gdiff AS sumdiff FROM table ORDER BY sumdiff DESC

Might need to worry about negatives too...don't know of a 2-argument max() function in sql, but using
CASE WHEN g>100 THEN g-100 ELSE 100-g AS gdiff
might work (in postgres anyway...does MySQL understand CASE?)