Find the closest number with all else equal

[TOA]

I tried to come up with an accurate title, so don't shoot me if it's not 

 

How can I select the closest number to another number (if that makes sense)?

 

I have products with a set of 4 numbers:

          field1  field2  field3  field4

p1        10      9        -2        2

p2        10      8          1        1

p3        8        8          0        2

p4        7        6        -1        3

 

(These are just examples)

 

This is meant to make a recommendation for a new product based on the users selection.

If the user selects p4, and wants to add 1 to field3, I want to select the p that is closest to the original, but matches the new field and keeps the rest of the numbers as close to original as possible.

 

I tried some things that got me p2 because its the first one the db reads that meets the conditions, but I want to select as close to the original as possible.

 

Man I hope this makes sense 

Your brain power is appreciated

[kickstart]

Hi

 

Not quite sure this is going to help. I have ignored adding one to field 3 of P4 for now (should be easy to add)

 

Depends on how you define close.

 

If you mean as close a match within field1 as poss, and within that as close a match within field2, and within that as close a match within field3, and within that as close a match within field4.

 

SELECT b.pField, ABS(a.field1 - b.field1) AS field1Diff, ABS(a.field2 - b.field2) AS field2Diff, ABS(a.field2 - b.field2) AS field3Diff, ABS(a.field2 - b.field2) AS field4Diff
FROM table1 a
CROSS JOIN table1 b
WHERE a.pField = 'P4'
AND b.pField <> 'P4'
ORDER BY field1Diff, field2Diff, field3Diff, field4Diff
LIMIT 1

 

If you mean the closest in the difference between all fields (ie, add all the differences) then

 

SELECT b.pField, (
ABS( a.field1 - b.field1 ) + ABS( a.field2 - b.field2 ) + ABS( a.field2 - b.field2 ) + ABS( a.field2 - b.field2 )
) AS fieldDiff
FROM table1 a
CROSS JOIN table1 b
WHERE a.pField = 'P4'
AND b.pField <> 'P4'
ORDER BY fieldDiff
LIMIT 1 

 

All the best

 

Keith

[TOA]

Yeah, its hard for me to put in english 

 

I think the first one is what I'm aiming at:

(I should clear up that they aren't truly changing anything, its a search to find a closely related product)

 

So if they choose p4 from the op, and want a product with a value 0 in field3 instead of -1, it would find the product that has a 0 in field 3, and keep the rest the same or as close as possible (optimally choosing p3 instead of p2). The changed field takes precedence. Hope that clears some things up

 

I'll see if that works and thank you