Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: Distinct ID and package

    Hi All,

    My source table contains data such as,

    ID package
    101 oap
    101 dealers
    101 cpo
    102 iap
    102 deals
    103 cpo
    103 deals
    104 nvi
    105 cpo

    The expected output is,

    ID package
    101 oap
    102 iap
    103 cpo
    104 nvi
    105 cpo

    when I try this, I get 2 rows for id 101. (expecting only 1)

    (SELECT DISTINCT ID, package
    FROM table1
    WHERE PACKAGE LIKE '%OAP%'
    OR PACKAGE LIKE '%IAP%'
    OR PACKAGE LIKE '%CPO%'
    OR PACKAGE LIKE '%NVI%')

    can someone please help me.

    Thanks
    pavan

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm making a lot of guesses, but I'd start with:
    Code:
    SELECT Max(ID) AS ID, PACKAGE
       FROM table1
       WHERE (PACKAGE LIKE '%OAP%'
          OR PACKAGE LIKE '%IAP%'
          OR PACKAGE LIKE '%CPO%'
          OR PACKAGE LIKE '%NVI%')
       GROUP BY PACKAGE;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2005
    Posts
    92

    Distinct ID and package

    I could not use max(ID) since the id:101 is same for all the 3 packages.

    if a package has both OAP, cpo then display only oap in the output.

    My source table contains data such as,

    ID package
    101 oap
    101 dealers
    101 cpo
    102 iap
    102 deals
    103 cpo
    103 deals
    104 nvi
    105 cpo

    The expected output is,

    ID package
    101 oap
    102 iap
    103 cpo
    104 nvi
    105 cpo
    Last edited by pavan_test; 01-19-14 at 23:10.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    The important qiuestion here is WHY is your expected output as it is? The information that you have supplied so far provide no logical basis for your output so you will need to explain clearly why you want
    101 oap
    instead of
    101 dealers
    and why you want
    102 iap
    and not
    102 deals.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pavan_test View Post
    I could not use max(ID) since the id:101 is same for all the 3 packages.
    Please try the code I suggested to see the results.

    My guess would be that either you will be pleasantly surprised or that you need to describe your requirements better.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Oct 2005
    Posts
    92

    Distinct ID and package

    I tried with max(ID), it did not work.

    when i tried with max(ID) i see the output as,

    101 oap
    101 dealers
    101 cpo

    where as the expected output is,

    ID package
    101 oap
    102 iap
    103 cpo
    104 nvi
    105 cpo


    when the ID has oap, then ignore all the other packages that includes cpo and display only oap. (oap is eligible to have cpo, hence i don't have to display cpo for ID 101).

    if a id has iap, display iap,
    if id has cpo display cpo (ignore rest of packages i.e. deals).

    thanks
    pavan

  7. #7
    Join Date
    Oct 2005
    Posts
    92

    Distinct ID and package

    Quote Originally Posted by pablolee View Post
    The important qiuestion here is WHY is your expected output as it is? The information that you have supplied so far provide no logical basis for your output so you will need to explain clearly why you want
    101 oap
    instead of
    101 dealers
    and why you want
    102 iap
    and not
    102 deals.
    when the ID has oap, then ignore all the other packages that includes cpo and display only oap. (oap is eligible to have cpo, hence i don't have to display cpo for ID 101).

    if a id has iap, display iap,
    if id has cpo display cpo (ignore rest of packages i.e. deals).

    thanks
    pavan

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though it might be required some amendments to conform to Oracle syntax,
    I got the result which you showed, like...
    ... the expected output is,

    ID package
    101 oap
    102 iap
    103 cpo
    104 nvi
    105 cpo
    , by the following query on DB2.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table1
    ( ID , package ) AS (
    VALUES
      ( 101 , 'oap'     )
    , ( 101 , 'dealers' )
    , ( 101 , 'cpo'     )
    , ( 102 , 'iap'     )
    , ( 102 , 'deals'   )
    , ( 103 , 'cpo'     )
    , ( 103 , 'deals'   )
    , ( 104 , 'nvi'     )
    , ( 105 , 'cpo'     )
    )
    SELECT id , package
     FROM  (SELECT id , package
                 , ROW_NUMBER()
                      OVER( PARTITION BY id
                                ORDER BY CASE package
                                         WHEN 'oap' THEN 1
                                         WHEN 'iap' THEN 2
                                         WHEN 'cpo' THEN 3
                                         ELSE            4
                                         END
                                       , package
                          ) AS candidate_order
             FROM  table1
             WHERE PACKAGE LIKE '%oap%'
               OR  PACKAGE LIKE '%iap%'
               OR  PACKAGE LIKE '%cpo%'
               OR  PACKAGE LIKE '%nvi%'
           )
     WHERE candidate_order = 1
    ;
    ------------------------------------------------------------------------------
    
    ID          PACKAGE
    ----------- -------
            101 oap    
            102 iap    
            103 cpo    
            104 nvi    
            105 cpo    
    
      5 record(s) selected.
    Last edited by tonkuma; 01-20-14 at 12:53.

  9. #9
    Join Date
    Oct 2005
    Posts
    92

    Distinct ID and package

    Thank you. it worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •