Mysql – Get Closest Value With GROUP BY

group byMySQL

I'm having issues with getting the closest result depending on the data that will be given by the user. The user gives me the Length, Width, and Height of a box then I compute the volume and compare them to the available volumes in the database grouped by country.

I have created the fiddle here.

+====+=====+==========+=================+===============+======+=====+======+
| id |BU_ID|country_id| Name            |Description    |Length|Width|Height|
+====+=====+==========+=================+===============+======+=====+======+
|164 |117  |1         |Jumbo Box        |Biggest        |24.00 |25.00|17.00 |
|165 |117  |1         |Regular          |Large Box      |24.00 |20.00|17.00 |
|166 |117  |1         |Medium           |Medium Box     |18.00 |18.00|16.00 |
|167 |117  |1         |Bulilit          |Small          |18.00 |9.00 |16.00 |
|254 |117  |2         |Bulilit Indonesia|Small Indonesia|18.00 |9.00 |16.00 |
|255 |117  |2         |Jumbo Indonesia  |Biggest        |24.00 |25.00|17.00 |
|256 |117  |2         |Medium Indonesia |Medium Box     |18.00|18.00 |16.00 |
|257 |117  |2         |Regular Indonesia|Large Box      |24.00|20.00 |17.00 |
+====+=====+==========+=================+===============+======+=====+======+

If the Length * Width * Height of the box that the user gave is 3375 then this should be the result:

+====+=====+==========+=================+===============+======+=====+======+
| id |BU_ID|country_id| Name            |Description    |Length|Width|Height|
+====+=====+==========+=================+===============+======+=====+======+
|166 |117  |1         |Medium           |Medium Box     |18.00 |18.00|16.00 |
|256 |117  |2         |Medium Indonesia |Medium Box     |18.00 |18.00|16.00 |
+====+=====+==========+=================+===============+======+=====+======+

I was able to get ALL the data that are equal or greater than 3375 but I can't get the 'closest' ones.

Any help is highly appreciated.

Best Answer

After trying multiple times and with the help of the answer here, I was able to get the right query:

SELECT t1.`id`, t1.`BU_ID`, t1.`country_id`, 
       t1.`Name`, t1.`Description`, t1.`Length`, 
       t1.`Width`, t1.`Height`, t3.min_lwh
FROM boxes AS t1
JOIN (SELECT MIN(t2.Length * t2.Width * t2.Height) AS min_lwh
      FROM boxes AS t2 WHERE (t2.Length * t2.Width * t2.Height) >= 3775
   ) AS t3
ON (t1.Length * t1.Width * t1.Height) = t3.min_lwh;
Related Question