sql - MySQL: strange behavior with GROUP BY in WHERE subselect -

i hope can me topic.

i have 1 table, relevant fields varchar id, varchar name , date

3df0001ab    testing_1    2017-04-04 3df0002zg    testing_2    2017-04-03 3df0003er    testing_1    2017-04-01 3df0004xy    testing_1    2017-03-26 3df0005uo    testing_3    2017-03-25 

the goal retrieve 2 entries every name (>500), sorted date. can use database queries tried following approach. 1 id every name, union result same query, excluding ids first set.

first step 1 entry every name. result expected, 1 id every name.

select id table group name; 

second step; using above statement in clause receive results, not in first result:

select id table id not in (select id table group name) 

but here result empty, tried invert using where id in instead of not in. expected result same ids show when using subquery, result ids table. assume subquery delivers wrong result, because when copy ids manually -> id in ("3df0001ab", ...) works.

so maybe can explain behavior and/or find solution original problem.

this bad practice:

select id table group name; 

although mysql allows construct, returned id indeterminate row. can different rows when run same query @ different times.

a better approach use aggregation function:

select max(id) table group name; 

your real problem, though, different. when use not in, no rows returned if any value in in list null. how not in defined.

i recommend using not exists or left join instead, because behavior more intuitive:

select t.id table t left join      (select max(id) id       table t2       group name      ) tt      on t.id = tt.id tt.id null;