CODEWITHSUNDEEP
mysqlsqlselectgroup-bysql-delete
Vityata
Vityata

Reputation: 43585

SELECT everything but the SELECTION from a table - MySQL

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)

Upvotes: 0

Views: 128

Answers (1)

Kevin Horgan
Kevin Horgan

Reputation: 1580

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;

Upvotes: 2

Related Questions