find closest numbers approximately












1












$begingroup$


I have a table like this:



TableTest



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 this: 4, 5.75, 7.2, 6 One human can find it (Row 1 is answer), But How can I write a query for Computer to find it?
I'm using SQL Server but i think it's relative machine learning






machine-learning regression sql







share|improve this question









New contributor




Armin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.







$endgroup$

















    1












    $begingroup$


    I have a table like this:



    TableTest



    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 this: 4, 5.75, 7.2, 6 One human can find it (Row 1 is answer), But How can I write a query for Computer to find it?
    I'm using SQL Server but i think it's relative machine learning






    machine-learning regression sql







    share|improve this question









    New contributor




    Armin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.







    $endgroup$















      1












      1








      1





      $begingroup$


      I have a table like this:



      TableTest



      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 this: 4, 5.75, 7.2, 6 One human can find it (Row 1 is answer), But How can I write a query for Computer to find it?
      I'm using SQL Server but i think it's relative machine learning






      machine-learning regression sql







      share|improve this question









      New contributor




      Armin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.







      $endgroup$




      I have a table like this:



      TableTest



      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 this: 4, 5.75, 7.2, 6 One human can find it (Row 1 is answer), But How can I write a query for Computer to find it?
      I'm using SQL Server but i think it's relative machine learning






      machine-learning regression sql




      machine-learning regression sql






      share|improve this question









      New contributor




      Armin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question









      New contributor




      Armin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question








      edited 2 days ago









      I_Play_With_Data

      1,177430




      1,177430






      New contributor




      Armin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 2 days ago









      ArminArmin

      61




      61




      New contributor




      Armin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      Armin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      Armin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          1 Answer
          1






          active

          oldest

          votes


















          1












          $begingroup$

          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






          share|improve this answer









          $endgroup$













          • $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
            2 days ago










          • $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
            2 days ago










          • $begingroup$
            Is this right? select Col1 , Power((Col1 - 5), 2) as mse_loss from MYTABLE
            $endgroup$
            – Armin
            2 days ago










          • $begingroup$
            I have tested this way and I didn't get a strong answer from it.
            $endgroup$
            – Armin
            2 days ago










          • $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
            yesterday











          Your Answer





          StackExchange.ifUsing("editor", function () {
          return StackExchange.using("mathjaxEditing", function () {
          StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
          StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
          });
          });
          }, "mathjax-editing");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "557"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });






          Armin is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f47151%2ffind-closest-numbers-approximately%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          1












          $begingroup$

          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






          share|improve this answer









          $endgroup$













          • $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
            2 days ago










          • $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
            2 days ago










          • $begingroup$
            Is this right? select Col1 , Power((Col1 - 5), 2) as mse_loss from MYTABLE
            $endgroup$
            – Armin
            2 days ago










          • $begingroup$
            I have tested this way and I didn't get a strong answer from it.
            $endgroup$
            – Armin
            2 days ago










          • $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
            yesterday
















          1












          $begingroup$

          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






          share|improve this answer









          $endgroup$













          • $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
            2 days ago










          • $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
            2 days ago










          • $begingroup$
            Is this right? select Col1 , Power((Col1 - 5), 2) as mse_loss from MYTABLE
            $endgroup$
            – Armin
            2 days ago










          • $begingroup$
            I have tested this way and I didn't get a strong answer from it.
            $endgroup$
            – Armin
            2 days ago










          • $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
            yesterday














          1












          1








          1





          $begingroup$

          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






          share|improve this answer









          $endgroup$



          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







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered 2 days ago









          Shamit VermaShamit Verma

          80426




          80426












          • $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
            2 days ago










          • $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
            2 days ago










          • $begingroup$
            Is this right? select Col1 , Power((Col1 - 5), 2) as mse_loss from MYTABLE
            $endgroup$
            – Armin
            2 days ago










          • $begingroup$
            I have tested this way and I didn't get a strong answer from it.
            $endgroup$
            – Armin
            2 days ago










          • $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
            yesterday


















          • $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
            2 days ago










          • $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
            2 days ago










          • $begingroup$
            Is this right? select Col1 , Power((Col1 - 5), 2) as mse_loss from MYTABLE
            $endgroup$
            – Armin
            2 days ago










          • $begingroup$
            I have tested this way and I didn't get a strong answer from it.
            $endgroup$
            – Armin
            2 days ago










          • $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
            yesterday
















          $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
          2 days ago




          $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
          2 days ago












          $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
          2 days ago




          $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
          2 days ago












          $begingroup$
          Is this right? select Col1 , Power((Col1 - 5), 2) as mse_loss from MYTABLE
          $endgroup$
          – Armin
          2 days ago




          $begingroup$
          Is this right? select Col1 , Power((Col1 - 5), 2) as mse_loss from MYTABLE
          $endgroup$
          – Armin
          2 days ago












          $begingroup$
          I have tested this way and I didn't get a strong answer from it.
          $endgroup$
          – Armin
          2 days ago




          $begingroup$
          I have tested this way and I didn't get a strong answer from it.
          $endgroup$
          – Armin
          2 days ago












          $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
          yesterday




          $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
          yesterday










          Armin is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          Armin is a new contributor. Be nice, and check out our Code of Conduct.













          Armin is a new contributor. Be nice, and check out our Code of Conduct.












          Armin is a new contributor. Be nice, and check out our Code of Conduct.
















          Thanks for contributing an answer to Data Science Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid …



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          Use MathJax to format equations. MathJax reference.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f47151%2ffind-closest-numbers-approximately%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          -

          Popular posts from this blog

          Callistus I

          Image
          Callistus I Res apud Vicidata repertae: Nativitas : unknown value ; Roma Obitus : 223 ; Roma Pontificatus 217–222 Praecessor : Zephyrinus Successor : Urbanus I Sanctus Callistus qui ieiunium instituit; e libro manu scripto Francogallico saeculo XIV Callistus (natus die ignoto; obiit anno 222), fuit episcopus Romae et Papa Ecclesiae Catholicae Romanae ab anno 217. Vita | Secundum adversarium suum, presbyterem, fuit Hippolytus Callistus, servus argentarii Christiani. Papae Zephyrino, Callistus Romae arcessivit. Callistus fuit primus diaconus ac dux catacumbae apud Viam Appiam, quae hodie Sancti Callisti appellatur. Pontificatus | Nexus externi | Vicimedia Communia plura habent quae ad Callistus I spectant. Fontes Latinae de papis usque ad annum 530 (Papa Felix IV) Liber pontificalis Opera Omnia .mw-parser-output .stipula{padding:3px;background:#F7F8FF;border:1px solid grey;margin:auto}.mw-parser-output .stipula td.cell1{bac...
          Read more

          Tabula Rosettana

          Image
          Tabula multilinguis Rosettana in Museo Britannico ostenditur. Tabula Rosettana, [1] etiam titulo OGIS 90 agnita, est stela decreto de rebus sacris in Aegypto anno 196 a.C.n. lato inscripta. Tabula iuxta Rosettam Aegypti, urbem in delta Nili et ad oram maris Mediterranei iacentem, anno 1799 a milite Francico reperta est. Inventio stelae, linguis duabus scripturisque tribus inscriptae, eruditis Instituti Aegypti statim nuntiata est; ibi enim iussu imperatoris Napoleonis eruditi omnium scientiarum (sub aegide Commissionis Scientiarum et Artium) properaverant cum expeditione Francica. Qua a Britannis mox debellata, tabula Rosettana Londinium missa hodie apud Museum Britannicum iacet. Textus Graecus cito lectus interpretationi textuum Aegyptiorum (in formis hieroglyphica et demotica expressorum) gradatim adiuvit. Denique textum plene interpretatus est Ioannes Franciscus Champollion. Ab opere eruditorum cumulativo coepit hodiernus scripturae hieroglyphicae linguaeque Aegyptiae a...
          Read more

          How to label and detect the document text images

          Image
          1 $begingroup$ This is what I mean as document text image: I want to label the texts in image as separate blocks and my model should detect these labels as classes. NOTE: This is how the end result should be like: The labels like Block 1, Block 2, Block 3,.. should be Logo, Title, Date,.. Others, etc. Work done: First approach : I tried to implement this method via Object Detection, it didn't work. It didn't even detect any text. Second approach : Then I tried it using PixelLink. As this model is build for scene text detection, it detected each and every text in the image. But this method can detect multiple lines of text if the threshold values are increased. But I have no idea how do I add labels to the text blocks. PIXEL_CLS_WEIGHT_all_ones = 'PIXEL_CLS_WEIGHT_all_ones' PIXEL_C...
          Read more