Uzochi Uzochi - 6 months ago 7
SQL Question

Remmove one of two Rows where first column is identical and second with different date

Imagine i have such a table :

Nr Date

2162416 14.02.2014
2162416 11.08.2006
2672007 13.04.2016
2672007 27.11.2007
3030211 31.01.2013
3030211 25.04.2006
3108243 11.04.2016
3108243 24.08.2009
3209248 05.04.2016
3209248 08.06.2012
3232333 11.04.2012
3232333 23.12.2011
3232440 08.04.2013
3232440 23.01.2008


as you can see, the entries are pairs which only differ on value of date column. How can i delete one of them by comparing date. I want to remove the old ones.

Answer

Simple way, use EXISTS to remove a row if another row with same Nr but later date exists:

delete from tablename t1
where exists (select 1 from tablename t2
              where t2.nr = t1.nr
                and t2.date > t1.date)

Alternatively:

delete from tablename
where (nr, date) not in (select nr, max(date) from tablename group by nr)
Comments