Get Latest Records of Duplicate Rows
Introduction
One of the topics that I faced in my work experience was when they user upload excel sheet as data entry to the system and some times may request to make updates within same period of time where the system must display latest record only for each month. So I start looking around on web with no result so after long thinking I found good and easy way which requires each table that take this action to display latest data contain entry date which already exist within my system design, and find main keys of that record to handle with as PK to join then with report query to display only latest records.
The Example
Lets take for example table with our data
(ID, EntryDate, Site, Locality, Ammount)
Data
[1, 2008-03-30 14:27:22.213, 1, 2, 1000]
[2, 2008-03-31 14:27:22.213, 1, 2, 1002]
[2, 2008-03-31 14:27:22.213, 2, 2, 1002]
So from data above we need only two records as following:
[2, 2008-03-31 14:27:22.213, 1, 2, 1002]
[2, 2008-03-31 14:27:22.213, 2, 2, 1002]
Select Site, Locality, Max(EntryDate)
From DataSourceTable
Group by Site, Locality
Then we store this data into temp table or as view and inner join with actual table on both keys to get latest data.
