SO 3, MySQL - rextester

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';

Drop table if exists t1;

create table t1(id integer,status integer,value integer);

insert into t1(id,status,value) values(1,10,100);
insert into t1(id,status,value) values(2,10,100);
insert into t1(id,status,value) values(10,10,100);
insert into t1(id,status,value) values(3,10,60);
insert into t1(id,status,value) values(4,11,20);
insert into t1(id,status,value) values(5,11,15);
insert into t1(id,status,value) values(6,12,100);
insert into t1(id,status,value) values(7,12,50);
insert into t1(id,status,value) values(8,12,50);
insert into t1(id,status,value) values(9,12,40);

Select *from t1;

select 1st.status as Status,
       SUBSTRING_INDEX(1st.id,'/',1) as 1stID,
       1st.value as 1stValue,
       (case when locate('/',1st.id) > 0 then SUBSTRING_INDEX(1st.id,'/',-1)
             else 2nd.id 
        end) as 2ndID,
        (case when locate('/',1st.id) > 0 then 1st.value
             else 2nd.value 
        end) as 2ndValue
        from
(
(select status, SUBSTRING_INDEX(Group_concat(id separator '/'),'/',2) as id,value
from t1
where (status,value) in (select status,value
from t1
group by status
having max(value))
group by status) 1st

inner join

(select status,id,value
from t1
where (status,value) not in (select status,value
from t1
group by status
having max(value))
group by status,value
order by status,value desc) 2nd
on 1st.status = 2nd.status)
group by 1st.status;


absolute service time: 0,34 sec