I have a database in Mysql 5.6 and I have a table where the drivers are registered, the database is used to manage the trips of a taxi site, it works in the following way:
A client requests a trip, and that trip is recorded in a table called travel_pendientes, and Applicants, in this table of Applicants the drivers are automatically registered, record information as distance between the user, quality of the driver and money accumulated throughout the day.
The trip will be assigned to the operator that is less than 1km away, but if there is more than 1 operator within that range then it will be given to the one less than 1km away and have better quality, and if so There are still more than 1 operator then it is given to the one that has less money accumulated in the day.
My problem is that in Mysql, if I indicate that I select the one that has less money accumulated with the MIN (money) sentence, you can select one that is outside the allowed kilometer, and if together the sentences (Distance < 1000) AND MAX (Quality) AND MIN (money), it sends me an error, or rather, it does not select any record, because the one with the best quality has the most money, or is out of the distance, etc.
Is it possible to evaluate the second (quality) condition only to the records that meet the first condition (distance)? and obviously, that only the third condition (money) is evaluated to those who already met the first two?