Q:

MYSQL Select single row by multiple parameters returning unexpected result

I have a table offers with five relevant columns, offer_id, offer_type, offer_amount, end_date and offer_status
I am trying to select the row with the highest offer_amount that matches the correct offer_type and an offer_status of 1 (active).
The query I am using is
SELECT * FROM offers_tbl WHERE offer_status = 1 AND offer_type = 'site-wide' AND offer_amount = (SELECT MAX(offer_amount) FROM offers_tbl )
Whats happening is that this is not returning any results if the MAX(offer_amount) happens to have a offer_status of 0 (inactive).
If the particular row with the highest MAX(offer_amount) happens to have an offer_status of 1, this works fine. Its only when the offer status is 0 that this breaks.

A:

Try making it find MAX out of active ones:
SELECT * FROM offers_tbl WHERE offer_status = 1 AND offer_type = 'site-wide' AND offer_amount = (SELECT MAX(offer_amount) FROM offers_tbl WHERE offer_status = 1)
Otherwise, it's finding MAX offer amounts that could have a status of 0, which makes the outside query not find a match of status of 1 AND that amount.

