0

Is there any way to make a UNION consider only specific columns when checking for duplicity?

The query below returns all of the columns that I need but, due to slight differences in some of the columns, the UNION results in duplicates based on the FULLPWSID column value. The only column that I would like UNION to consider is FULLPWSID. The order of the tables being queried ensures that I get the most up to date information.

SELECT TRIM(TINWSYS.NUMBER0) AS FULLPWSID,
       TINWSYS.NAME AS SYSNAME,
       TINWSYS.D_PRIN_CITY_SVD_NM AS SYSCITY,
       TINWSYS.D_PRIN_CNTY_SVD_NM AS COUNTY,
       TINWSYS.ACTIVITY_STATUS_CD AS STATUS
       FROM TINWSYS
WHERE (TINWSYS.NAME                   LIKE '%SCENIC WOLF LAUREL #%'   OR
       TINWSYS.D_PRIN_CITY_SVD_NM LIKE '%SCENIC WOLF LAUREL #%'   OR
       TINWSYS.NUMBER0            LIKE '%SCENIC WOLF LAUREL #%')
UNION
SELECT CONCAT('NC',TO_CHAR(PWSID)) AS FULLPWSID,
PRNEW_PWS_INFO.PWS_NAME    AS SYSNAME,
PRNEW_PWS_INFO.CITY_SVD_NM AS SYSCITY,
PRNEW_PWS_INFO.CNTY_SVD_NM AS COUNTY,
TO_CHAR('P')
FROM PRNEW_PWS_INFO
WHERE (PRNEW_PWS_INFO.PWS_NAME  LIKE '%SCENIC WOLF LAUREL #%'   OR
       PRNEW_PWS_INFO.PWSID     LIKE '%SCENIC WOLF LAUREL #%')
ORDER BY FULLPWSID

The results of the query are:

FULLPWSID   SYSNAME                     SYSCITY     COUNTY  STATUS
NC1058009   SCENIC WOLF LAUREL #1                   MADISON A
NC1058010   SCENIC WOLF LAUREL #2                   MADISON A
NC1058011   SCENIC WOLF LAUREL #3       MARS HILL   MADISON A
NC1058012   SCENIC WOLF LAUREL #4                   MADISON A
NC1058015   SCENIC WOLF LAUREL #5       MARS HILL   MADISON A
NC1058015   SCENIC WOLF LAUREL #5A      MARS HILL   MADISON P
NC1058016   SCENIC WOLF LAUREL #6A      MARS HILL   MADISON P
NC1058017   SCENIC WOLF LAUREL #7                   MADISON A
NC1058017   SCENIC WOLF LAUREL #7A      MARS HILL   MADISON P
NC1058018   SCENIC WOLF LAUREL #8       MARS HILL   MADISON A
NC1058018   SCENIC WOLF LAUREL #8A      MARS HILL   MADISON P
NC1058021   SCENIC WOLF LAUREL #9       MARS HILL   MADISON A

As you can see there are 3 duplicates in the FULLPWSID column. I can take care of it in the underlying code but it would be helpful and cleaner to let the query take care of it.

DDL for the PRNEW_PWS_INFO table:

PWSID   NUMBER(7)
PWS_NAME    VARCHAR2(40)
PWS_ST_TYPE_CD  VARCHAR2(4)
CITY_SVD_NM VARCHAR2(40)
CNTY_SVD_NM VARCHAR2(12)
OWNER_NAME  VARCHAR2(40)
OWNER_ADD1  VARCHAR2(40)
OWNER_ADD2  VARCHAR2(40)
OWNER_CITY  VARCHAR2(40)
OWNER_STATE VARCHAR2(2)
OWNER_ZIP   VARCHAR2(10)
OWNER_BUS_PHN   VARCHAR2(12)
APPLICANT_NAME  VARCHAR2(40)
APPLICANT_ADD1  VARCHAR2(40)
APPLICANT_ADD2  VARCHAR2(40)
APPLICANT_CITY  VARCHAR2(40)
APPLICANT_STATE VARCHAR2(2)
APPLICANT_ZIP   VARCHAR2(10)
APPLICANT_BUS_PHN   VARCHAR2(12)
NCID    VARCHAR2(20)
DATE_ASSIGNED   TIMESTAMP(5)
TINWSYS_IS_NUMBER   NUMBER(7)

DDL for the TINWSYS table:

NUMBER0 CHAR(12)
TINWSYS_IS_NUMBER   NUMBER(7)
TINWSYS_ST_CODE CHAR(2)
ALTERNATE_ST_NUM    VARCHAR2(15)
NAME    VARCHAR2(40)
LOCAL_NAME  VARCHAR2(40)
ACTIVITY_STATUS_CD  CHAR(1)
ACTIVITY_DATE   DATE
OWNER_TYPE_CODE CHAR(1)
D_PRIN_CITY_SVD_NM  VARCHAR2(40)
D_PRIN_CNTY_SVD_NM  VARCHAR2(40)
SURF_WTR_RATIO  NUMBER(5,2)
SURF_WTR_PUR_RATIO  NUMBER(5,2)
GRND_WTR_RATIO  NUMBER(5,2)
GRND_WTR_PUR_RATIO  NUMBER(5,2)
GRND_WTR_UDI_RATIO  NUMBER(5,2)
GRND_WTR_UDI_PURCH  NUMBER(5,2)
OPER_CATEGORY_CD    VARCHAR2(5)
D_FED_PRIM_SRC_CD   CHAR(3)
D_ST_PRIM_SRC_CD    CHAR(3)
D_POPULATION_COUNT  NUMBER(10)
D_HISTORY_IND_CD    CHAR(1)
D_TTL_STOR_CAP_MSR  NUMBER(15,3)
PWS_ST_TYPE_CD  CHAR(4)
D_PWS_FED_TYPE_CD   CHAR(4)
D_LAST_INV_UPDT_TS  DATE
D_LAST_UPDT_TS  DATE
D_USERID_CODE   CHAR(8)
LAST_SNTY_SRV_DT    DATE
D_PRT_GW_SR_IND_CD  CHAR(1)
D_TCR_MCL_VIO_DT    DATE
D_INV_CHG_IND_CD    CHAR(1)
D_DSNFCT_GW_IND_CD  CHAR(1)
D_SWGUDI_FL_IND_CD  CHAR(1)
ACTIVITY_REASON_CD  CHAR(2)
DAYS_SERVING_COUNT  NUMBER(3)
D_NEW_WS_FLAG   CHAR(1)
D_INITIAL_TS    DATE
D_INITIAL_USERID    CHAR(8)
D_CDS_TRIGGER_TS    TIMESTAMP(6)
D_CDS_TRIGGER_USER  CHAR(8)
ACTIVITY_RSN_TXT    VARCHAR2(2000)
MEMO_TEXT   VARCHAR2(2000)
STAGE2_CDS_ID   VARCHAR2(22)
  • And what should the other columns show when the checking column matches but the other ones differ? – ypercubeᵀᴹ Oct 25 '18 at 19:39
  • Based on the order of the tables in the union I would expect the first table to be returned. As the query stands now the order of the rows is correct and I can "pick off" the first row and get the correct data ignoring the duplicate. – Dave Oct 25 '18 at 19:51
  • @Dave - there is no such thing as "order" in the database unless you supply the ORDER BY clause. 12c, by default, will run both clauses concurrently. I suggest you don't fall into the same trap some people did back in ~8i/9i: GROUP BY implies ORDER BY – Michael Kutz Oct 25 '18 at 22:45
  • Thanks for that information Michael. If GROUP BY is used in this situation will the rows from the first table referenced (TINWSYS) be selected over the rows in the other table? – Dave Oct 25 '18 at 23:00
1

You can select from your UNION'd result set and GROUP BY the single column you would like to be unique. Because you can only have one record returned for the other columns, you must aggregate with MAX().

SELECT U.FULLPWSID, 
MAX(U.SYSNAME) AS SYSNAME, 
MAX(U.SYSCITY) AS SYSCITY, 
MAX(U.COUNTY) AS COUNTY, 
MAX(U.STATUS) AS STATUS
FROM (
    SELECT TRIM(TINWSYS.NUMBER0) AS FULLPWSID,
           TINWSYS.NAME AS SYSNAME,
           TINWSYS.D_PRIN_CITY_SVD_NM AS SYSCITY,
           TINWSYS.D_PRIN_CNTY_SVD_NM AS COUNTY,
           TINWSYS.ACTIVITY_STATUS_CD AS STATUS
           FROM TINWSYS
    WHERE (TINWSYS.NAME                   LIKE '%SCENIC WOLF LAUREL #%'   OR
           TINWSYS.D_PRIN_CITY_SVD_NM LIKE '%SCENIC WOLF LAUREL #%'   OR
           TINWSYS.NUMBER0            LIKE '%SCENIC WOLF LAUREL #%')
    UNION
    SELECT CONCAT('NC',TO_CHAR(PWSID)) AS FULLPWSID,
    PRNEW_PWS_INFO.PWS_NAME    AS SYSNAME,
    PRNEW_PWS_INFO.CITY_SVD_NM AS SYSCITY,
    PRNEW_PWS_INFO.CNTY_SVD_NM AS COUNTY,
    TO_CHAR('P')
    FROM PRNEW_PWS_INFO
    WHERE (PRNEW_PWS_INFO.PWS_NAME  LIKE '%SCENIC WOLF LAUREL #%'   OR
           PRNEW_PWS_INFO.PWSID     LIKE '%SCENIC WOLF LAUREL #%')
    ) U
GROUP BY U.FULLPWSID
ORDER BY U.FULLPWSID
  • Thanks for the suggestion and query Chad. Attempting to run that query resulted in ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 25 Column: 7 which is the line with ) AS U – Dave Oct 25 '18 at 19:10
  • It is Oracle, it doesn't like the AS in the table alias. – ypercubeᵀᴹ Oct 25 '18 at 19:44
  • @Dave the answer has been updated with proper syntax to alias the table in Oracle. Please try again and let me know if it works for you. – Chad Cahill Oct 25 '18 at 21:12
  • Sorry for the delayed response @ChadCahill ... your update did in fact work but as Michael pointed out in a comment to the original question the results are not what I need/expected and I am going to have to pursue this differently. Thank you for your complete answer though. – Dave Oct 30 '18 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.