I have below sample data, and I'm trying to get groups of record with below conditions:
- Only one record must have
- Number of records in each group must be greater than one
- The record with
payment_idmust have the least
Here is my sample data:
||237003000||2021-07-03 01:00:00||2021-09-01 01:00:00|
|2||10||1741||139664000||2021-07-03 01:00:00||2021-11-16 01:00:00|
||0||2021-07-04 01:00:00||2021-08-02 01:00:00|
|4||20||1655||6820000||2021-07-04 01:00:00||2021-09-06 01:00:00|
||39000||2021-07-04 01:00:00||2021-09-08 01:00:00|
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.