how can I apply a DISTINCT to this query?

0
SELECT ADM.GROUP_ID, ADM.GROUP_NAME, ADM.CUG_ID, ADM.ADMIN_SUBS_ID, 
    ADM.MSISDN MSISDN_ADMIN, ADM.SUBSCRIPTION_STATE ADMIN_STATE,
    SUBS.MSISDN MEMBER_MSISDN, SUBS.SUBSCRIPTION_ID, 
    SUBS.SUBSCRIPTION_STATE MEMBER_STATE,
    SUBS.GROUP_ID_1, SUBS.GROUP_ID_2, SUBS.GROUP_ID_3
FROM 
    (SELECT DISTINCT  GROUP_ID, GROUP_NAME, CUG_ID, ADMIN_SUBS_ID,
        MSISDN, SUBSCRIPTION_STATE, GROUP_ID_1, GROUP_ID_2, GROUP_ID_3
    FROM PMS.PMS_GROUP G
        LEFT JOIN PMS.PMS_SUBSCRIPTION S
        ON SUBSCRIPTION_ID = ADMIN_SUBS_ID
    WHERE SUBSCRIPTION_STATE = 5) ADM
LEFT JOIN
    PMS.PMS_SUBSCRIPTION SUBS
    ON (ADM.GROUP_ID = SUBS.GROUP_ID_1
        OR ADM.GROUP_ID = SUBS.GROUP_ID_2
        OR ADM.GROUP_ID = SUBS.GROUP_ID_3)
WHERE ADM.MSISDN <> SUBS.MSISDN
AND SUBS.SUBSCRIPTION_STATE = 2
ORDER BY ADM.GROUP_ID;

If I apply it, the query comes back and shows me the repeated data
What I want is to keep me from repeating data

    
asked by yeko garcia 16.04.2018 в 23:33
source

3 answers

0

Your query does not return duplicate information. Oracle considers a duplicate record when "the values of all the fields are identical in a single record". In the example that shows if there are duplicate values for the ADMIN_SUBS_ID field, however, the SUBS.MSISDN and SUBS.SUBSCRIPTION_ID fields make the records unique (not repeated).

In case you need to be grouped by this field, I recommend you make a DISTINCT (in the top selection) omitting these fields, however, keep in mind that the rest of the fields may contain at some point different values that make the records unique.

    
answered by 17.04.2018 / 00:00
source
0

The Distinct must be applied in a SELECT .

In a table or set of tables the results of a column obtained through a SELECT statement can appear repeatedly, with DISTINCT said column will show a single result for each different value .

SELECT DISTINCT ADM.GROUP_ID, ADM.GROUP_NAME, ADM.CUG_ID, ADM.ADMIN_SUBS_ID, 
    ADM.MSISDN MSISDN_ADMIN, ADM.SUBSCRIPTION_STATE ADMIN_STATE,
    SUBS.MSISDN MEMBER_MSISDN, SUBS.SUBSCRIPTION_ID, 
    SUBS.SUBSCRIPTION_STATE MEMBER_STATE,
    SUBS.GROUP_ID_1, SUBS.GROUP_ID_2, SUBS.GROUP_ID_3

FROM...

With the above code you will only get one result for each different ADM.GROUP_ID.

    
answered by 17.04.2018 в 00:02
0

The simplest solution is to make a sub-query:

SELECT DISTINCT * FROM (TU_QUERY);

If that still does not work, you should evaluate which field of your query causes the DISTINCT not to de-duplicate them, you probably have some condition in the ON or WHERE.

Remember that the DISTINCT only de-duplicates if and only if all the columns in the query are equal, if you do not have to apply a GROUP BY of the key fields and use some aggregation function (SUM, MAX, etc.) in the rest of the fields.

To list the duplicate records you can use:

SELECT GROUP_ID,COUNT('*') FROM (TU_QUERY) GROUP BY GROUP_ID HAVING COUNT('*')>1;
    
answered by 17.04.2018 в 00:13