how to select distinct records from multiple records with union tables

Question

I was selecting set of columns from difference table using union. Below is the sql script of MySQL. I need to select only the recent inserted records based on contactId and created_date.

SELECT contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action,screenid
FROM
  (SELECT `contactId` AS contactId, `createdDate` AS created_date,
          CASE
              WHEN `riskRatingFlag`='1' THEN 'HIGH'
              WHEN `riskRatingFlag`='0' THEN 'LOW'
              ELSE NULL
          END AS riskRating_Flag,
          CASE
              WHEN `pepFlag`='1' THEN 'YES'
              WHEN `pepFlag`='0' THEN 'NO'
              ELSE NULL
          END AS pep_Flag,
          `remarks` AS remarks,
          'ResolveHit_T' AS `tablename`,
            'Initiate Review' AS action,
          `resolveHitId` AS screenid
   FROM `ast_CustomersScreenedResolveHit_T`
   UNION SELECT `contactId` AS contactId, `createdDate` AS created_date,
                CASE
                    WHEN `riskRatingFlag`='1' THEN 'HIGH'
                    WHEN `riskRatingFlag`='0' THEN 'LOW'
                    ELSE NULL
                END AS riskRating_Flag,
                CASE
                    WHEN `pepFlag`='1' THEN 'YES'
                    WHEN `pepFlag`='0' THEN 'NO'
                    ELSE NULL
                END AS pep_Flag,
                `remarks` AS remarks,
                'CountryRisk_T' AS `tablename`,
'Initiate Review' AS action,
                `countryRiskId` AS screenid
   FROM `ast_CustomersScreenedCountryRisk_T`
   UNION SELECT `contactId` AS contactId, `createdDate` AS created_date,
                CASE
                    WHEN `riskRatingFlag`='1' THEN 'HIGH'
                    WHEN `riskRatingFlag`='0' THEN 'LOW'
                    ELSE NULL
                END AS riskRating_Flag,
                CASE
                    WHEN `pepFlag`='1' THEN 'YES'
                    WHEN `pepFlag`='0' THEN 'NO'
                    ELSE NULL
                END AS pep_Flag,
                `remarks` AS remarks,
                'OccupationRisk_T' AS `tablename`,
'Initiate Review' AS action,
                `occupationRiskId` AS screenid
   FROM `ast_CustomersScreenedOccupationRisk_T`) AS `result`

ORDER BY `result`.`created_date` DESC

The expected output is mentioned in the below image expected result

But the result I got is result what I got

maybe this is another discussion about "what is your concept of distinct" — Drew, Oct 02 '16 at 05:25
What if you have multiple rows with same contactID and created Date ?? In that case which row you gonna choose?? — Esty, Oct 02 '16 at 05:25
Try using MAX() to get the most recent records based on contactId and date. In that case, no need to use distinct. — AT-2017, Oct 02 '16 at 06:01
@TanjimRahman, if multiple rows with the same contactID, then i need to select only first row i need to get based on the create date — Arun, Oct 02 '16 at 06:17
@AT-2016 if I use MAX(), I will get only one recent record. I need recent records of distinct contactID. — Arun, Oct 02 '16 at 06:25
MAX() will have the recent or distinct values. Just do a GROUP BY with contactId and date. — AT-2017, Oct 02 '16 at 06:39

Esty · Answer 1 · 2016-10-02T06:42:06.937

It will work but see my comment in code 'You may need more column here'. If multiple row exists with same contactId and created_Date you need to add other columns to get your exact output.

select contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action, screenid
from (
    select contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action, screenid,
        @running:=if(@previous=contactId,@running,0) + 1 as rownum,
        @previous:=contactId
    from 
    (
        SELECT contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action,screenid
        FROM
          (
            SELECT `contactId` AS contactId, `createdDate` AS created_date,
              CASE
                  WHEN `riskRatingFlag`='1' THEN 'HIGH'
                  WHEN `riskRatingFlag`='0' THEN 'LOW'
                  ELSE NULL
              END AS riskRating_Flag,
              CASE
                  WHEN `pepFlag`='1' THEN 'YES'
                  WHEN `pepFlag`='0' THEN 'NO'
                  ELSE NULL
              END AS pep_Flag,
              `remarks` AS remarks,
              'ResolveHit_T' AS `tablename`,
                'Initiate Review' AS action,
              `resolveHitId` AS screenid
           FROM `ast_CustomersScreenedResolveHit_T`
           UNION 
           SELECT `contactId` AS contactId, `createdDate` AS created_date,
            CASE
                WHEN `riskRatingFlag`='1' THEN 'HIGH'
                WHEN `riskRatingFlag`='0' THEN 'LOW'
                ELSE NULL
            END AS riskRating_Flag,
            CASE
                WHEN `pepFlag`='1' THEN 'YES'
                WHEN `pepFlag`='0' THEN 'NO'
                ELSE NULL
            END AS pep_Flag,
            `remarks` AS remarks,
            'CountryRisk_T' AS `tablename`,
            'Initiate Review' AS action,
            `countryRiskId` AS screenid
           FROM `ast_CustomersScreenedCountryRisk_T`
           UNION SELECT `contactId` AS contactId, `createdDate` AS created_date,
            CASE
                WHEN `riskRatingFlag`='1' THEN 'HIGH'
                WHEN `riskRatingFlag`='0' THEN 'LOW'
                ELSE NULL
            END AS riskRating_Flag,
            CASE
                WHEN `pepFlag`='1' THEN 'YES'
                WHEN `pepFlag`='0' THEN 'NO'
                ELSE NULL
            END AS pep_Flag,
            `remarks` AS remarks,
            'OccupationRisk_T' AS `tablename`,
            'Initiate Review' AS action,
            `occupationRiskId` AS screenid
           FROM `ast_CustomersScreenedOccupationRisk_T`) AS `result`

        ORDER BY `result`.`created_date` DESC
    ) t
    order by contactId, created_date DESC -- You may need to add more column here
) g
WHERE g.rownum = 1;

You may also use ROW_NUMBER() OVER(PARTITION BY __ ORDER BY __) but for multiple row with same contactId and creation_date it will not work.

select contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action, screenid
from (
    select contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action, screenid,
        ROW_NUMBER() OVER (PARTITION BY contactId ORDER BY created_date DESC) AS rownum
    from 
    (
        SELECT contactId, created_date, riskRating_Flag, pep_Flag, remarks, tablename, action,screenid
        FROM
          (
            SELECT `contactId` AS contactId, `createdDate` AS created_date,
              CASE
                  WHEN `riskRatingFlag`='1' THEN 'HIGH'
                  WHEN `riskRatingFlag`='0' THEN 'LOW'
                  ELSE NULL
              END AS riskRating_Flag,
              CASE
                  WHEN `pepFlag`='1' THEN 'YES'
                  WHEN `pepFlag`='0' THEN 'NO'
                  ELSE NULL
              END AS pep_Flag,
              `remarks` AS remarks,
              'ResolveHit_T' AS `tablename`,
                'Initiate Review' AS action,
              `resolveHitId` AS screenid
           FROM `ast_CustomersScreenedResolveHit_T`
           UNION 
           SELECT `contactId` AS contactId, `createdDate` AS created_date,
            CASE
                WHEN `riskRatingFlag`='1' THEN 'HIGH'
                WHEN `riskRatingFlag`='0' THEN 'LOW'
                ELSE NULL
            END AS riskRating_Flag,
            CASE
                WHEN `pepFlag`='1' THEN 'YES'
                WHEN `pepFlag`='0' THEN 'NO'
                ELSE NULL
            END AS pep_Flag,
            `remarks` AS remarks,
            'CountryRisk_T' AS `tablename`,
            'Initiate Review' AS action,
            `countryRiskId` AS screenid
           FROM `ast_CustomersScreenedCountryRisk_T`
           UNION SELECT `contactId` AS contactId, `createdDate` AS created_date,
            CASE
                WHEN `riskRatingFlag`='1' THEN 'HIGH'
                WHEN `riskRatingFlag`='0' THEN 'LOW'
                ELSE NULL
            END AS riskRating_Flag,
            CASE
                WHEN `pepFlag`='1' THEN 'YES'
                WHEN `pepFlag`='0' THEN 'NO'
                ELSE NULL
            END AS pep_Flag,
            `remarks` AS remarks,
            'OccupationRisk_T' AS `tablename`,
            'Initiate Review' AS action,
            `occupationRiskId` AS screenid
           FROM `ast_CustomersScreenedOccupationRisk_T`) AS `result`

        ORDER BY `result`.`created_date` DESC
    ) t
) g
WHERE g.rownum = 1;

I was using MariaDB. I was getting the bellow mentioned error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'running:=if(previous=contactId,running,0) + 1 as rownum, previous:=' at line 4 — Arun, Oct 02 '16 at 06:15
I don't see any tag of MariaDB in your question. What I see is MySQL !!!! — Esty, Oct 02 '16 at 06:29
Well you have to declare the variables. Its good you are using maraiDB cause you may have use the power of partition. See updated answer. — Esty, Oct 02 '16 at 06:32
Sorry try it again. I have resolve the syntax error. Also see the update about rowumber. — Esty, Oct 02 '16 at 06:46
thanks for your support. But still I was getting the bellow error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(PARTITION BY contactId ORDER BY created_date DESC) AS rownum from ( ' at line 4 — Arun, Oct 02 '16 at 07:01

how to select distinct records from multiple records with union tables

1 Answers1