MySQL SELECT MAX in equation

What I'm attempting to do find values in a table which are less than the MAX of another field, minus a numeric value. Eg:

   ...WHERE some_table.value_1 = 0 AND another_table.value_2 <= (SELECT MAX(another_table.value_3) - 5) ORDER BY...开发者_高级运维

However, this is not working! My joins are all fine, and the query runs without the 2nd part of the WHERE statement, but if you'd like to see the rest of the code for more info, let me know!

Cheers!

Sparkles*

ps all the values are integers

---

Here is a working example using joins, try to apply it to yours:

SELECT        *
FROM          table1 t1
INNER JOIN    table2 t2
    ON        t1.join_field = t2.join_field
WHERE         t1.some_field = 1
    AND       t2.other_field <= (
                  SELECT (MAX(t22.third_field) - 5)
                  FROM   table2 t22
              );

If this is not exactly what you were looking for, please let me know and I will update it.

---

Use HAVING MAX(...)

Something like:

SELECT MIN(p.price) AS price, p.pricegroup
FROM articles_prices AS p
_YOUR_JOINED_TABLE_
WHERE p.articleID=10
GROUP BY p.pricegroup
HAVING MAX(p.price) > _VALUE_FROM_JOINED_TABLE_;