CODEWITHSUNDEEP
phpmysql
Perocat
Perocat

Reputation: 1521

Get the most near value mysql

I have 3 tables:

PRICE

id   price   date
1     50     20130716
2     30     20130717

TVA

id    val      start      end
1      7     20080101   20103112
2      8     20110101

MARGIN

id    qty    marg
1     500     25
2     600     20
3     800     15
4     1000    13
5     1250    11
...

Now I have this query which doesn't works:

$quantity = '557';

$link->query("
       SELECT (
              (price+marg)*((val+100)/100)
              )
       FROM PRICE
         JOIN TVA
         JOIN MARGIN
       WHERE date = '20130717' 
             AND end = ''
             AND qty = '$quantity'
");

The problem is that there isn't a qty = '557' on the table. What I'd like to do is to select the most near quantity to (in this case) '557'.

So if:

$quantity = '557' the query should select 600

$quantity = '701' the query should select 800

$quantity = '1238' the query should select 1250

etc.

Is this possible?

Upvotes: 0

Views: 261

Answers (3)

Russell Uhl
Russell Uhl

Reputation: 4531

If you put it into a procedure, you can use something like this (sort-of pseudocode): 

CREATE PROCEDURE pDoStuff(@target INTEGER)
AS

    DELCARE @qty INTEGER

    -- Get closest qty
    @qty = SELECT TOP 1 qty
            FROM table
            ORDER BY ABS(@target - qty) ASC


    -- use that "actual" qty in your query
    SELECT  ((price+marg)*((val+100)/100)
    FROM    price
    JOIN    TVA
    JOIN    MARGIN
    WHERE   date = 'thedate'
    AND     end = ''
    AND     qty = @qty

GO

The syntax is incorrect, but it gives you an idea. This will allow you to select ALL rows from your original query with the closest quantity value. Most of the other answers here will limit your final results to one row (which may or may not be what you actually want).

Upvotes: 1

Jerzy Zawadzki
Jerzy Zawadzki

Reputation: 1985

You can get value bigger than yours, ordered ascending and limit to 1 result. so you can firt value bigger or equal yours

  SELECT (
              (price+marg)*((val+100)/100)
              )
       FROM PRICE
         JOIN TVA
         JOIN MARGIN
       WHERE date = '20130717' 
             AND end = ''
             AND qty >= '$quantity'
ORDER BY qty ASC LIMIT 1

With same method you can get value lower than your number and see which one is closer to your qty

Upvotes: 0

liding
liding

Reputation: 116

...
AND `qty` <= $quantity
ORDER BY `qty` DESC
LIMIT 1

Upvotes: 0

Related Questions