Geek asks and answers

SQL statement to eliminate duplicates based on the value in another column

I have data that are in two tables, and I have a query that combines data, as shown below. I am trying to eliminate duplicates based on the Id column, where I select the record with the oldest schedule date. Can someone kindly help me with an SQL statement for 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 follows:

|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       |

Additional Information:
Let me explain what I'm trying to do here.

, secId ( ) .
splitdetails, , , secId () , .

:
- - secId, , 1.
- secId Splits, , , , , , .

, :
| ID | SecId | ReportingDate | |
| 1469 2 31--08 0,1
| 1465 2 31--09 0,1
| 1466 2 31-Dec-10 0,2
| 1468 2 31-Dec-11 0.2
| 1467 2 31-Dec-12 1

, , -        SELECT Gotten.ID, Gotten.SecID, Gotten.ReportingDate, Gotten.SplitDate, Adjustor

(SELECT tblFundamentalsDetails.id, tblFundamentalsDetails.SecId, tblFundamentalsDetails.ReportingDate, tblSplitDetails.SplitDate, tblSplitDetails.Adjustor FROM tblFundamentalsDetails
LEFT JOIN tblSplitDetails
ON (tblFundamentalsDetails.ReportingDate

0
3

, Access SQL ( ):

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

qry Access.

0

( ) []

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

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


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
+1

MySQL, , :

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

, . .

0

More articles:


All Articles