How to define advance condition in each group

I have below sample data, and I’m trying to get groups of record with below conditions:

• Same prev_read_at
• Different current_read_at
• Only one record must have payment_id
• Number of records in each group must be greater than one
• The record with payment_id must have the least amount

Here is my sample data:

I’ve applied all conditions except the last one in below query.

select GROUP_CONCAT(id), location_id, GROUP_CONCAT(current_read_at) from bills
where prev_read_at between '2021-07-01 12:01:01' and '2021-07-05 12:01:01'
group by CONCAT(`location_id` ,`prev_read_at`)
having count(id)>1 AND count(payment_id)=1 AND COUNT(DISTINCT current_read_at)>1

The desirable result must contain only records with location_id = 10.
I think the last condition must be applied on each group, but don’t know how to implement this in having or are there any other method for this?

Server version: 10.3.18-MariaDB – MariaDB Server

Here is the dbfiddle of my question. As you can see, I want to filter records with ids of 3,4,5 in my result. Because 3,4,5 group don’t have payment_id with the least amount in its group.

You should consider using window functions for applying such complex conditions. After select your criteria over different partitions, you can limit your results over those criteria. Below query is derived by considering your conditions in this fiddle.

SELECT * FROM (
select *,
    COUNT(payment_id) OVER (PARTITION BY prev_read_at,location_id) payed_count,
    COUNT(location_id) OVER (PARTITION BY prev_read_at,location_id) loc_count,
    min(case when payment_id is null then amount end) OVER (PARTITION BY prev_read_at,location_id) min_amt,
    min(case when payment_id is not null then amount end) OVER (PARTITION BY prev_read_at,location_id) payed_min_amt,
        DENSE_RANK() OVER (PARTITION BY location_id, prev_read_at ORDER BY current_read_at ASC) cur_rank
from bills
where prev_read_at between '2021-07-01 12:01:01' and '2021-07-05 12:01:01'
) A
    WHERE payed_count=1
    AND loc_count>payed_count
    AND payed_min_amt+1 < min_amt+1
    AND cur_rank > 1
    AND payment_id is not null;

Hope it works.