Field 1 is duplicated on field 2, need to select 1 based on field 3, and return both field 1 and 2

Question

I have a list of data with 3 fields, ISIN, CompanyId, and EffectiveDate. ISIN gets duplicated on both CompanyId and Effective Date.

Example Data:

ISIN            CompanyId    EffectiveDate
AED001410045    58105545     2012-02-09
AEDFXA1G39P8    132844116    2016-04-22
AEDFXA1G39P8    132844116    2017-09-21
ARDEUT110020    249603       2012-02-09
ARDEUT110020    416264458    2017-03-22

Using

SELECT ISIN, CompanyId, MAX(EffectiveDate)
FROM [MappingData].[dbo].[ESGMappingISIN]
GROUP BY ISIN,CompanyId

I can remove the duplicates on ISIN and CompanyId to return

ISIN            CompanyId    EffectiveDate
AED001410045    58105545     2012-02-09
AEDFXA1G39P8    132844116    2017-09-21
ARDEUT110020    249603       2012-02-09
ARDEUT110020    416264458    2017-03-22

The last step that I need is to:

A) Return ISIN and CompanyId where ISIN is distinct
B) If 1 ISIN has 2 CompanyIds, return the line with the later effective date.

Ideal output

ISIN            CompanyId    EffectiveDate
AED001410045    58105545     2012-02-09
AEDFXA1G39P8    132844116    2017-09-21
ARDEUT110020    416264458    2017-03-22

Use a subquery to get the rows you want, join back to main tables to get data columns from there. Same logic here: https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date/19433107#19433107 — Twelfth, Oct 12 '17 at 17:44

score 1 · Answer 1 · answered Oct 12 '17 at 17:50

1

You can use row_number():

SELECT *
FROM (SELECT ISIN, CompanyId, MAX(EffectiveDate) as maxed,
             ROW_NUMBER() OVER (PARTITION BY ISIN ORDER BY MAX(Effectivedate) DESC) as seqnum
      FROM [MappingData].[dbo].[ESGMappingISIN]
      GROUP BY ISIN, CompanyId
     ) m
WHERE seqnum = 1;

answered Oct 12 '17 at 17:50

Gordon Linoff

1,242,037
58
646
786

@Twelfth . . . This is ANSI standard SQL syntax, which seems reasonable when there isn't a specific database tag. – Gordon Linoff Oct 13 '17 at 01:23

score 1 · Answer 2 · answered Oct 12 '17 at 20:55

You can use subqueries like this:

SELECT
   B.ISIN,
   B.CompanyID,
   B.EffectiveDate 
FROM
   (
      SELECT
         ISIN,
         CompanyId,
         MAX(EffectiveDate) AS EffectiveDate 
      FROM
         ESGMappingISIN 
      GROUP BY
         ISIN,
         CompanyId 
   )
   AS B 
   JOIN
      (
         SELECT
            ISIN,
            MAX(EffectiveDate) AS EffectiveDate 
         FROM
            ESGMappingISIN
         GROUP BY ISIN 
      ) AS A 
      ON B.ISIN = A.ISIN 
      AND B.EffectiveDate = A.EffectiveDate

Field 1 is duplicated on field 2, need to select 1 based on field 3, and return both field 1 and 2

2 Answers2