CODEWITHSUNDEEP
sqlms-accessgroup-byaggregate-functions
Avagut
Avagut

Reputation: 994

SQL Statement to eliminate duplicates based on value in another column

I have data that is in two tables and I have a query combining the data as below. I am trying to eliminate duplicates based on the Id column where I pick the record with the oldest split date. Could anyone kindly assist me with the SQL statement to effect this? 

|ID     |SecID  |ReportingDate  |SplitDate  |Adjustor|  
|1465   |2      |31-Dec-09      |01-Nov-10  |0.1     |  
|1465   |2      |31-Dec-09      |27-Dec-12  |0.2     |  
|1466   |2      |31-Dec-10      |27-Dec-12  |0.2     |   
|1468   |2      |31-Dec-11      |27-Dec-12  |0.2     |  
|1469   |2      |31-Dec-08      |01-Nov-10  |0.1     |  
|1469   |2      |31-Dec-08      |27-Dec-12  |0.2     |

The result should be as below: 

|ID     |SecId  |ReportingDate  |Adjustor  |  
|1469   |2      |31-Dec-08      |0.1       |  
|1465   |2      |31-Dec-09      |0.1       |  
|1466   |2      |31-Dec-10      |0.2       |  
|1468   |2      |31-Dec-11      |0.2       |

More Information:
Let me explain what I am trying to do here.

In the fundamentals table I have a row with a unique Line Id, secId( a product identifier) and a reporting date for this line.
This information needs to be adjusted using information from the splitdetails table that has a date from which it becomes applicable, the secId(product) it affects and the adjustor ratio to be used.

For each line in fundamentals table:
-Where any secId that doesnt have an entry in the splits table, adjustor should be 1.
-If secId is present in the Splits table, the split to be used is the oldest one whose date is older than the fundamentals table reporting date being checked.

I am hoping to get results from the sample above would end up look like this:
| ID |SecId |ReportingDate |Adjustor |
|1469 2 31-Dec-08 0.1
|1465 2 31-Dec-09 0.1
|1466 2 31-Dec-10 0.2
|1468 2 31-Dec-11 0.2
|1467 2 31-Dec-12 1

The query I am using is
SELECT Gotten.ID, Gotten.SecID, Gotten.ReportingDate, Gotten.SplitDate, Adjustor
FROM
(SELECT tblFundamentalsDetails.id, tblFundamentalsDetails.SecId, tblFundamentalsDetails.ReportingDate, tblSplitDetails.SplitDate, tblSplitDetails.Adjustor FROM tblFundamentalsDetails
LEFT JOIN tblSplitDetails
ON (tblFundamentalsDetails.ReportingDate

Upvotes: 0

Views: 151

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123829

For test data in a table (or saved query) named [Source]

ID    SecID  ReportingDate  SplitDate   Adjustor
----  -----  -------------  ----------  --------
1465      2  2009-12-31     2010-11-01       0.1
1465      2  2009-12-31     2012-12-27       0.2
1466      2  2010-12-31     2012-12-27       0.2
1468      2  2011-12-31     2012-12-27       0.2
1469      2  2008-12-31     2010-11-01       0.1
1469      2  2008-12-31     2012-12-27       0.2

the following query has been tested and actually does work in Access:

SELECT 
    Source.ID,
    Source.SecID,
    Source.ReportingDate,
    Source.Adjustor
FROM
    Source
    INNER JOIN
    (
        SELECT ID, MIN(SplitDate) AS MinOfSplitDate
        FROM Source
        GROUP BY ID
    ) AS MinDate
        ON MinDate.ID = Source.ID
            AND MinDate.MinOfSplitDate = Source.SplitDate

returning

ID    SecID  ReportingDate  Adjustor
----  -----  -------------  --------
1465      2  2009-12-31          0.1
1466      2  2010-12-31          0.2
1468      2  2011-12-31          0.2
1469      2  2008-12-31          0.1

Upvotes: 1

Olvathar
Olvathar

Reputation: 551

I can do this on MySQL server, if I understood you this is what you need:

DELETE t1 FROM mytable t1, mytable t2 WHERE t1.ID = t2.ID 
AND t1.ReportingDate < t2.ReportingDate

This will only keep rows in mytable with the newest date and same id

Notice that three rows with same ID will become just one. You'll only keep one row for each ID.

Upvotes: 0

cjb110
cjb110

Reputation: 1491

One approach, that should be ok in Access SQL (though you might need to change the aliases):

SELECT a.*
FROM Table1 a
INNER JOIN
(
SELECT ID, sdate = min(SplitDate)
FROM Table1
GROUP BY ID
) b
ON a.ID = b.ID
AND a.SplitDate = b.sdate

Also you could take the inner query and make its own qry in Access.

Upvotes: 0

Related Questions