mysql query, getting max value with some other conditions

devels

an example to explain my problem:

user input is total amount, is for this example 26

I have a sql table like this:

     product_id         amount                     price

         2                  1                          310,00
         2                10                          300,00
         2                25                          290,50
         2                50                          270,00
         2                60                          230,00

What I need is:
the price of the nearby amount of the total_amount.

So if the total_amount is 26 I need to get amount 25 from the table and with that value I need to get the price: 290,50

My own idea was to get first the amount values from the table that are equel or lower than teh total_amount:

SELECT aantal
FROM table
WHERE product_id = '2'
     AND amount <= '$total_amount'

And then to get the highest value of the result with the php function max() and with that highest amount value a new query to get the price related to that highest amount value.

But there is also a MAX() function in SQL and I strongly believe there is a solution for one query to get this done. I already have tried some querys but it is not working.

BTW: I do visit this forum frequently but today I have registererd me, hello 🙂

pohopo

try this which will give you the closest amount that is lower or equal to your total amount.

SELECT price
FROM table
WHERE product_id = '2'
AND amount in
(SELECT max(amount) 
FROM table 
WHERE amount <= $total_amount)

devels

Originally posted by pohopo
try this which will give you the closest amount that is lower or equal to your total amount.
SELECT price
FROM table
WHERE product_id = '2'
AND amount in
(SELECT max(amount) 
FROM table 
WHERE amount <= $total_amount)
[/B]

Thanks! That seems to be the solution but a little problem occurs
It is a subquery, and when I test each query alone it does do what it must do.
But I get problems when I use it in mysql

query I used:

SELECT price
FROM table
WHERE product_id = '2'
AND amount IN
    (SELECT MAX(amount)
     FROM table
     WHERE amount <= '26')

Error:

You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(amount)
     FROM table
     WHERE amount

I dont get it each query within the subquery works fine by itself but only together it cause problems. Any solution?

devels

Can't I have a MAX() in a subquery?
Because it says there is something wrong with the syntax

SELECT price
FROM table
WHERE product_id = '2'
AND amount IN
    (SELECT MAX(amount)
     FROM table
     WHERE amount <= '26')

Sorry for the bump but I really can't get out bymyself. And I really appreciate of someone can help me.