I have a table like this; all columns are numeric:


Col1  Col2  Col3  Col4
  5    6     7     8
 12    6     5     6
  2    3.5   6     1

And I want to find the closest row with these values: 4, 5.75, 7.2, 6. A human can find it (row 1 is answer), but how can I write a query for a computer to find it?

I'm using SQL Server but I think it's relevant to machine learning.


This problem can be implemented as a query that minimizes "loss" from the inputs. If "mean square error" loss is used, loss calculation will look like :

select col1, col2, (col1 - inp1)^2 + (col2 - inp2)^2 as mse_loss, 
from table 

Now, you can find the row with least value of mse_loss

  • $\begingroup$ I get an error: The data type numeric and int are incompatible in the '^' operator. I'm using numeric. Doesn't this way work on numeric or float? $\endgroup$ – Armin Mar 12 '19 at 12:17
  • $\begingroup$ This is not actual syntax. You have to use POWER function for calculation of square. docs.microsoft.com/en-us/sql/t-sql/functions/… $\endgroup$ – Shamit Verma Mar 12 '19 at 12:28
  • $\begingroup$ Is this right? select Col1 , Power((Col1 - 5), 2) as mse_loss from MYTABLE $\endgroup$ – Armin Mar 12 '19 at 13:09
  • $\begingroup$ I have tested this way and I didn't get a strong answer from it. $\endgroup$ – Armin Mar 12 '19 at 16:13
  • $\begingroup$ In the given example, row 1 has least loss (losses are 5.1 , 68.9 and 35.5 for rows 1 , 2 and 3). Can you post data where you did not get good results. $\endgroup$ – Shamit Verma Mar 13 '19 at 6:09

I'll use table-valued variables because they're easier to use in a demo. This will work exactly the same with temporary tables or proper tables.

declare @Data table(
    Col1 numeric(5, 2),
    Col2 numeric(5, 2),
    Col3 numeric(5, 2),
    Col4 numeric(5, 2)
insert @Data
    ( 5, 6  , 7,   8),
    (12, 6  , 5,   6),
    ( 2, 3.5, 6,   1),
    ( 1, 4  , 5, 500);

declare @Probe table(
    Col1 numeric(5, 2),
    Col2 numeric(5, 2),
    Col3 numeric(5, 2),
    Col4 numeric(5, 2)
insert @Probe
    (4, 5.75, 7.2, 6);

    Comparison =ABS(d.col1 - p.Col1) +
                ABS(d.Col2 - p.Col2) +
                ABS(d.Col3 - p.Col3) +
                ABS(d.Col4 - p.Col4)
from @Data as d
cross apply @Probe as p
order by Comparison;

I put the sought-for values in a table (@Probe) because that is more generally useful than hard-coding values in the query.

You don't specify what your distance function is so I've used a simple difference. Any other calculation can be substituted for the definition of column "Comparison".

CROSS APPLY says "for each row from the left side (i.e. @Data) use the row from the right side (i.e. @Probe)". It also works if the right-side refers to a T-SQL function which would allow for more complex calculation. Since the sought-for values are a single row a CROSS JOIN would work just as well here.

Finally the ORDER BY sorts the output by closeness with the closest at the head of the list. If two data rows produce the same Comparison value it is indeterminate which will be at the head of the list.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.