mysql - Select matching to two columns of subquery -

background:

given table t1 fields a, b (and others):

drop temporary table if exists t1;  create temporary table t1 (id integer not null auto_increment primary key, varchar(255), b int, c varchar(40));  insert t1 (a, b, c) select 'aa', 11, 100 union select 'bb', 12, 200 union select 'bb', 12, 201 union select 'aa', 12, 300 union select 'aa', 11, 101;  -- id   b  c --  1  aa 11 100 --  2  bb 12 200 --  3  bb 12 201 --  4  aa 12 300 --  5  aa 11 101 

---

goal: given combination of , b, examine how many rows there in t1, , list rows (to understand same , different between rows).

(finally, beyond scope of question, writing queries process some of older rows, determined "obsolete" (replaced recent row given , b). not safe combinations of , b, @ time. definitive answer on "what combinations of , b safe delete old version of" not available me - legacy table has associated many gbs of external files, of no longer relevant anyone. files have been backed up; need make conservative proposal files remove, , how determined files.)

i've made temp table t2 distinct combinations of , b (plus id, , count of how many rows of each combo):

drop temporary table if exists t2;  create temporary table t2 (id integer not null auto_increment primary key, varchar(255), b int) select count(1) cnt, a, b t1 group a, b order cnt desc; 

select * t2

-- id  cnt   b --  1    2  aa 11 --  2    2  bb 12 --  3    1  aa 12 

---

the query having trouble writing:

in actual data, there hundreds of rows combinations. interested in combinations have high count, attempt dump rows of t1, based on first row of t2:

select * t1 a=     (select t2 limit 1 offset 0) ,     b=     (select b t2 limit 1 offset 0); 

this gives error:

error code: 1137. can't reopen table: 't2' 

i presume should refer row want t2:

(select a, b t2 limit 1 offset 0) 

and make nested query uses row twice, in 2 places columns , b used. stuck on how write query. basic idea in head is:

select * t1     a=t3.a , b=t3.b in         (select a, b t2 limit 1 offset 0) t3; 

(which not valid sql)

note: "offset 0" there because change other offset values, examine other a-b combos.

the goal see response:

-- id   b  c --  1  aa 11 100 --  5  aa 11 101 

---

or maybe can done join, i'm not sure how join using 1 row of t2.

you can this:

  select t1.*     t1     join ( select t2.a, t2.b              t2             order t2.a, t2.b             limit 1 offset 0          ) t3    t3.a = t1.a      , t3.b = t1.b 

without order by clause, mysql free return row. need add order make result deterministic.