CODEWITHSUNDEEP
sqloracle-database
mnu-nasir
mnu-nasir

Reputation: 1760

Eliminate duplicate rows by outer joining two table in Oracle 11i

I have following two table with sample Data.

PLACED_PERSON_INFO

         *PLACED_PERSON_INFO_GUID                 CPR*
                 P1                           0201026157
                 P2                           0309929493
                 P3                           0002170000
                 P4                           0000011037
                 P5                           1201006694
                 P6                           1201009887
                 P7                           1110007144
                 P8                           0309906353
                 P9                           0101002420

PLACED_PERSON_PLACES

 *PP_ID   PLACEMENT_DATE         PLACEMENT_STOP    PLACED_PERSON_INFO_GUID*
   1       01-01-2014              31-12-2014              P1
   2       01-01-2014              31-12-2014              P1
   3       01-01-2013              31-12-2013              P2
   4       01-06-2014              30-10-2014              P3
   5       01-02-2014              30-10-2014              P3
   6       01-01-2013              01-01-2015              P4
   7       01-01-2013              30-05-2013              P4
   8       01-01-2012              30-03-2013              P5

I have written the following SQL Query to get the result combining these two tables.

SQL Query :

SELECT
PPI.PLACED_PERSON_INFO_GUID, PPI.CPR
FROM PLACED_PERSON_PLACES PPP, PLACED_PERSON_INFO PPI
WHERE (PPP.PLACEMENT_DATE <= SYSDATE OR PPP.PLACEMENT_DATE IS NULL)
AND (PPP.PLACEMENT_STOP >= SYSDATE OR PPP.PLACEMENT_STOP IS NULL)
AND PPP.PLACED_PERSON_INFO_GUID (+) = PPI.PLACED_PERSON_INFO_GUID
ORDER BY PPI.CPR;

Query Result:

        PLACED_PERSON_INFO_GUID             CPR
              P1                          0201026157
              P1                          0201026157
              P3                          0002170000
              P3                          0002170000
              P4                          0000011037
              P6                          1201009887
              P7                          1110007144
              P8                          0309906353
              P9                          0101002420

But I want the following result where duplicate rows will not be shown. I do not want to use DISTINCT keyword. Can anyone help me in this result? I am using Oracle 11i.

Expected Result:

               PLACED_PERSON_INFO_GUID              CPR
                        P1                      0201026157
                        P3                      0002170000
                        P4                      0000011037
                        P6                      1201009887
                        P7                      1110007144
                        P8                      0309906353
                        P9                      0101002420

Upvotes: 1

Views: 4647

Answers (2)

mnu-nasir
mnu-nasir

Reputation: 1760

Solution is :

SELECT PLACED_PERSON_INFO_GUID, CPR
FROM (SELECT PPI.PLACED_PERSON_INFO_GUID, PPI.CPR,
      ROW_NUMBER() OVER (PARTITION BY PPI.PLACED_PERSON_INFO_GUID, PPI.CPR ORDER BY PPI.CPR) AS SEQNUM
      FROM PLACED_PERSON_INFO PPI LEFT JOIN PLACED_PERSON_PLACES PPP
      ON PPP.PLACED_PERSON_INFO_GUID = PPI.PLACED_PERSON_INFO_GUID                
      WHERE (PPP.PLACEMENT_DATE <= SYSDATE OR PPP.PLACEMENT_DATE IS NULL)
      AND (PPP.PLACEMENT_STOP >= SYSDATE OR PPP.PLACEMENT_STOP IS NULL)
     ) P
WHERE SEQNUM = 1
ORDER BY CPR

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

First, you should write your query using explicit join syntax:

SELECT PPI.PLACED_PERSON_INFO_GUID, PPI.CPR
FROM PLACED_PERSON_INFO PPI LEFT JOIN
     PLACED_PERSON_PLACES PPP
     ON PPP.PLACEMENT_DATE <= SYSDATE AND 
        PPP.PLACEMENT_STOP >= SYSDATE AND
        PPP.PLACED_PERSON_INFO_GUID = PPI.PLACED_PERSON_INFO_GUID
ORDER BY PPI.CPR;

If you only want one row, then you can use row_number():

SELECT PLACED_PERSON_INFO_GUID, CPR
FROM (SELECT PPI.PLACED_PERSON_INFO_GUID, PPI.CPR,
             ROW_NUMBER() OVER (PARTITION BY PPI.PLACED_PERSON_INFO_GUID, PPI.CPR ORDER BY PPI.CPR) as seqnum
      FROM PLACED_PERSON_INFO PPI LEFT JOIN
           PLACED_PERSON_PLACES PPP
           ON PPP.PLACEMENT_DATE <= SYSDATE AND 
              PPP.PLACEMENT_STOP >= SYSDATE AND
              PPP.PLACED_PERSON_INFO_GUID = PPI.PLACED_PERSON_INFO_GUID
     ) p
WHERE seqnum = 1;
ORDER BY CPR;

You can add additional columns and still only get one row per pair.

Upvotes: 1

Related Questions