0

I don't understand why the price range selection result is incorrect by using the HAVING Clause and the MIN() and MAX() aggregation functions together.

Here below an example:

SELECT * FROM table t
WHERE t.b_id=10
GROUP BY t.t_id  
HAVING min(t.price)> 0 AND max(t.price)<16
ORDER BY t.id DESC LIMIT 100 OFFSET 0

The result shows also rows with price values higher than 16, it seems like an out of range selection. Can someone explain me why MySQL doesn't select in range?

If I remove the MIN() and MAX() then the range selection seems correct.

UgoL
  • 839
  • 2
  • 13
  • 37

1 Answers1

1

I believe there is ambiguity to process the range using Min & Max. I mean when you know the min and max value then writing below code is not making sense in the processing.

min(t.price)> 0 AND max(t.price)<16

Thanks! Regards Ravi

Ravi Mishra
  • 11
  • 1
  • I don't agree. The query isn't ambiguous. `SELECT t.t_id, min(t.price), max(t.price)` would return a number of rows where min can be > 0 and max < 16. Something like min > 10 AND max < 5 would be logically impossible (i.e. `false`, "ambiguous" isn't the right word). – Gert Arnold Jan 08 '23 at 14:30