Mysql select one record from group having one of three conditions

Question

Using MySQL. I need to extract one record per group of storeID + profitCenter according to the following conditions:

1) If group has a single active record (active=1), select that record

2) If group has no active records select the most recent (max(id))

3) If group has multiple active records, select the most recent active record

Sample table: (blank lines added for clarity)

 ID  | storeID | profitCenter | active
 --------------------------------------
 1   | X       | 1            |    1
 2   | X       | 1            |    0
 3   | X       | 1            |    0

 4   | X       | 2            |    0
 5   | X       | 2            |    1

 6   | X       | 3            |    0
 7   | X       | 3            |    0
 8   | X       | 3            |    0

 9   | X       | 4            |    1
10   | X       | 4            |    1
11   | X       | 4            |    0

12   | X       | 5            |    1

13   | X       | 6            |    0

Desired result:

 ID  | storeID | profitCenter | active
------------------------------------------ 
 1   | X       | 1            |    1
 5   | X       | 2            |    1
 8   | X       | 3            |    0
10   | X       | 4            |    1
12   | X       | 5            |    1
13   | X       | 6            |    0

A single query would be great but I'm thinking I'll need to do this in a stored procedure due to its complexity. I would need to write the records to a new table so I can use that in joins and such. So far all I've really got is...

...A simple query to pull the "single" records like so....

 SELECT * FROM table GROUP BY storeID, profitCenter HAVING count(*) = 1 AND active = 1
UNION
SELECT * FROM table GROUP BY storeID, profitCenter HAVING count(*) = 1 AND active = 0

...which yields...

 ID  | storeID | profitCenter | active
------------------------------------------ 
12   | X       | 5            |    1
13   | X       | 6            |    0

Then I can do...

 SELECT sum(active),tmult.* FROM
      (SELECT t.* FROM t LEFT JOIN
           (SELECT t.* FROM t GROUP BY storeID, profitCenter HAVING count(*) = 1 AND active = 1
      UNION
           SELECT t.* FROM t GROUP BY storeID, profitCenter HAVING count(*) = 1 AND active = 0) tsing
      ON t.id = tsing.id
      WHERE tsing.id IS NULL) tmult
 GROUP BY storeid, profitCenter
 HAVING sum(active)=1;

...which gives the active record for each group that has only one active record:

 ID  | storeID | profitCenter | active
 --------------------------------------
 1   | X       | 1            |    1
 5   | X       | 2            |    1

It's the groups that have either no active records or multiple active records that I can't even begin to figure out.

Am I taking the right approach? Should I even be attempting this in SQL? I could write a script to do it but I was hoping to not have to go that route.

Any advice would be appreciated.

Answer 1

I believe something like this should do it:

 select 
    ifnull(max(active_records.id), max(all_records.id)) id,
    all_records.store_id,
    all_records.profit_center,
    if(max(active_records.id) is null, 0, 1) active
from
    store all_records
    left outer join store active_records on all_records.id = active_records.id and active_records.active = 1
group by
    all_records.store_id,
    all_records.profit_center
;

A full example with ddl and inserts is shown below:

create table store (
    id int,
    store_id varchar(64),
    profit_center int,
    active bit
);

insert into store values ( 1   , 'X'       , 1            ,    1);
insert into store values ( 2   , 'X'       , 1            ,    0);
insert into store values ( 3   , 'X'       , 1            ,    0);
insert into store values ( 4   , 'X'       , 2            ,    0);
insert into store values ( 5   , 'X'       , 2            ,    1);
insert into store values ( 6   , 'X'       , 3            ,    0);
insert into store values ( 7   , 'X'       , 3            ,    0);
insert into store values ( 8   , 'X'       , 3            ,    0);
insert into store values ( 9   , 'X'       , 4            ,    1);
insert into store values (10   , 'X'       , 4            ,    1);
insert into store values (11   , 'X'       , 4            ,    0);
insert into store values (12   , 'X'       , 5            ,    1);
insert into store values (13   , 'X'       , 6            ,    0);

select 
    ifnull(max(active_records.id), max(all_records.id)) id,
    all_records.store_id,
    all_records.profit_center,
    if(max(active_records.id) is null, 0, 1) active
from
    store all_records
    left outer join store active_records on all_records.id = active_records.id and active_records.active = 1
group by
    all_records.store_id,
    all_records.profit_center
;

+ ------- + ------------- + ------------------ + ----------- +
| id      | store_id      | profit_center      | active      |
+ ------- + ------------- + ------------------ + ----------- +
| 1       | X             | 1                  | 1           |
| 5       | X             | 2                  | 1           |
| 8       | X             | 3                  | 0           |
| 10      | X             | 4                  | 1           |
| 12      | X             | 5                  | 1           |
| 13      | X             | 6                  | 0           |
+ ------- + ------------- + ------------------ + ----------- +
6 rows

Answer 2

You can try this with union all.

SELECT * 
FROM TABLE
WHERE (storeID, profitCenter, Id) IN (
SELECT storeID, profitCenter, MAX(case when active=1 then id end) as maxid
FROM table 
GROUP BY storeID, profitCenter
HAVING SUM(active) >= 1
UNION ALL
SELECT storeID, profitCenter, MAX(id)
FROM table 
GROUP BY storeID, profitCenter
HAVING SUM(active) = 0
)