Reputation: 99
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
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