CODEWITHSUNDEEP
phpmysqldatabase
Seph1603
Seph1603

Reputation: 99

Remove Duplicate Row Except The Last One

I have this table on MySql:

Table1

ID    CODE     USER       NUMBER     ADJUSTMENT     ADJUST_DATE 
1     abc      Frank      10245      1              2015/04/20
2     def      Jonathan   25410      0              2015/04/21
3     ghi      Karen      55214      3              2015/05/05
4     abc      Frank      10245      2              2015/04/21
5     abc      Frank      10245      4              2015/04/22

I would like to remove the duplicated data and leave the last entry by date:

ID    CODE     USER       NUMBER     ADJUSTMENT     ADJUS_DATE 
2     def      Jonathan   25410      0              2015/04/21
3     ghi      Karen      55214      3              2015/05/05
5     abc      Frank      10245      4              2015/04/22

CODE, USER, NUMBER, ADJUSTMENT, ADJUS_DATE are 'Unique'

I need to create a temporary table with the result because I need all the records.

Upvotes: 0

Views: 58

Answers (1)

xQbert
xQbert

Reputation: 35353

Generate a subset of the max date grouped by like values in columns and join back to the base set...

SELECT A.ID, A.Code, A.user, A.Number, A.Adjustment, A.Adjust_date
FROM  table1 A
INNER JOIN (SELECT Code, User, Number, max(adjust_date) mDate
            FROM table1 group by Code, User, Number) B
  on A.code = B.code 
 and A.user = B.User 
 and A.Number = B.Number 
 and A.Adjust_date = B.mdate

Upvotes: 2

Related Questions