SELECT everything but the SELECTION from a table - MySQL

Question

Having a table, structured like this:

id	bar	foo	created_at	month_year
1	A	10	7/7/1999	jan2020
2	B	20	7/7/1999	jan2020
3	C	30	7/7/1999	jan2020
4	A	40	7/7/1999	jan2021
5	A	50	7/7/2000	jan2021
6	A	60	7/7/2000	feb2021

I used to run a query like this one:

delete ns
from foo ns
inner join 
(
    select month_year, max(nsa.created_at) created_at
    from foo nsa 
    group by month_year
) 
ns1 on ns1.month_year = ns.month_year and ns1.created_at <> ns.created_at;

The idea of that query was that it used to delete the values that were created_at last, based on unique month_year. Anyway, it was working the way I wanted.

The question:

What I need - without deletion, to get the values that were not deleted from that query. Tried to replace delete with select and I only managed to get the values that I needed to delete (which makes a lot of sense, actually), but I want to get exactly the values, that I did not get from that query.

(Feel free to edit the title to something making more sense in SQL)

Answer 1

Try a LEFT OUTER JOIN as follows

select ns.*
from foo ns
left outer join 
(
   select month_year, max(nsa.created_at) created_at
   from foo nsa 
   group by month_year
 ) 
ns1 on ns1.month_year = ns.month_year and ns1.created_at <> 
ns.created_at
where ns1.month_year IS NULL;