Help Please - eliminate dups

  • From Input create Output table with primary key of Org, Acct, Class & Prod

    Note that Acct 000059 records are already unique

         and that Acct 0038959 are dupes w/diff notes

         need first note instance in those cases

         and that Acct 0000256 contains 3 duplicate

         records and I need just 3 of the 9 records.

    From Table

    org  acct     class  prod    note            htmltext       

    ---- -------- ------ ------- --------------- ---------------

    10   0000059  WEB    CORPGIF Macy’s flagship Macy’s flagship

    10   0000059  WEB    RETAILS Macy’s flagship Macy’s flagship

    10   0000059  WEB    SHOPDEP Macy’s flagship Macy’s flagship

    10   0000059  WEB    SPOUSE  Macy’s flagship Macy’s flagship

    10   0038959  WEB    AVSVCS  Rental of compu Rental of compu

    10   0038959  WEB    AVSVCS  Rental of LCD/o Rental of LCD/o

    10   0038959  WEB    COMPREN Rental of compu Rental of compu

    10   0038959  WEB    COMPREN Rental of LCD/o Rental of LCD/o

    10   0038959  WEB    OFFEQUI Rental of compu Rental of compu

    10   0038959  WEB    OFFEQUI Rental of LCD/o Rental of LCD/o

    10   0000256  MEPG   DECORAT One of the Bay  One of the Bay

    10   0000256  MEPG   DECORAT One of the Bay  One of the Bay

    10   0000256  MEPG   DECORAT One of the Bay  One of the Bay

    10   0000256  MEPG   PRODCOS One of the Bay  One of the Bay

    10   0000256  MEPG   PRODCOS One of the Bay  One of the Bay

    10   0000256  MEPG   PRODCOS One of the Bay  One of the Bay

    10   0000256  MEPG   STAGING One of the Bay  One of the Bay

    10   0000256  MEPG   STAGING One of the Bay  One of the Bay

    10   0000256  MEPG   STAGING One of the Bay  One of the Bay

    (19 row(s) affected)

    Output needs to be:

    org  acct     class  prod    note            htmltext       

    ---- -------- ------ ------- --------------- ---------------

    10   0000059  WEB    CORPGIF Macy’s flagship Macy’s flagship

    10   0000059  WEB    RETAILS Macy’s flagship Macy’s flagship

    10   0000059  WEB    SHOPDEP Macy’s flagship Macy’s flagship

    10   0000059  WEB    SPOUSE  Macy’s flagship Macy’s flagship

    10   0038959  WEB    AVSVCS  Rental of compu Rental of compu

    10   0038959  WEB    COMPREN Rental of compu Rental of compu

    10   0038959  WEB    OFFEQUI Rental of compu Rental of compu

    10   0000256  MEPG   DECORAT One of the Bay  One of the Bay

    10   0000256  MEPG   PRODCOS One of the Bay  One of the Bay

    10   0000256  MEPG   STAGING One of the Bay  One of the Bay

    {This is 10 rows, where 9 rows from input table are discarded as dups.)

    I don't know if I can do this using selects or if I need to use a cursor, in either case I need an example as

    I've worked on this for four days and posted the problem twice and my brain is fried.

  • From what you've posted, I'm guessing

    SELECT DISTINCT

    org, acct, class, prod, note, htmltext  

    FROM table

    ORDER BY acct DESC, prod

     

    would work.

  • Here's solution thanks to poster on my other post

    SELECT ORG,

     ACCT,

     CLASS,

     PROD

     MIN(NOTE_TEXT) AS NOTE_TEXT

     MIN(HTML_TEXT) AS HTML_TEXT

    FROM INPUT

    GROUP BY

     ORG,

     ACCT,

     CLASS,

     PROD

    Thanks ALL

  • CREATE TABLE #Temp(org INT NOT NULL,

     acct VARCHAR(7) NOT NULL,

     class VARCHAR(4) NOT NULL,

     prod VARCHAR(7) NOT NULL,

     note VARCHAR(16) NOT NULL,

     htmltext VARCHAR(16) NOT NULL,

     PRIMARY KEY (org, acct, class, prod, note)

    )

    INSERT INTO #Temp (org, acct, class, prod, note, htmltext) 

    SELECT 10, '0000059', 'WEB', 'CORPGIF', 'Macy''s flagship', 'Macy''s flagship'

    UNION SELECT 10, '0000059', 'WEB', 'RETAILS', 'Macy''s flagship', 'Macy''s flagship'

    UNION SELECT 10, '0000059', 'WEB', 'SHOPDEP', 'Macy''s flagship', 'Macy''s flagship'

    UNION SELECT 10, '0000059', 'WEB', 'SPOUSE', 'Macy''s flagship', 'Macy''s flagship'

    UNION SELECT 10, '0038959', 'WEB', 'AVSVCS', 'Rental of compu', 'Rental of compu'

    UNION SELECT 10, '0038959', 'WEB', 'AVSVCS', 'Rental of LCD/o', 'Rental of LCD/o'

    UNION SELECT 10, '0038959', 'WEB', 'COMPREN', 'Rental of compu', 'Rental of compu'

    UNION SELECT 10, '0038959', 'WEB', 'COMPREN', 'Rental of LCD/o', 'Rental of LCD/o'

    UNION SELECT 10, '0038959', 'WEB', 'OFFEQUI', 'Rental of compu', 'Rental of compu'

    UNION SELECT 10, '0038959', 'WEB', 'OFFEQUI', 'Rental of LCD/o', 'Rental of LCD/o'

    UNION SELECT 10, '0000256', 'MEPG', 'DECORAT', 'One of the Bay', 'One of the Bay'

    UNION SELECT 10, '0000256', 'MEPG', 'DECORAT', 'One of the Bay', 'One of the Bay'

    UNION SELECT 10, '0000256', 'MEPG', 'DECORAT', 'One of the Bay', 'One of the Bay'

    UNION SELECT 10, '0000256', 'MEPG', 'PRODCOS', 'One of the Bay', 'One of the Bay'

    UNION SELECT 10, '0000256', 'MEPG', 'PRODCOS', 'One of the Bay', 'One of the Bay'

    UNION SELECT 10, '0000256', 'MEPG', 'PRODCOS', 'One of the Bay', 'One of the Bay'

    UNION SELECT 10, '0000256', 'MEPG', 'STAGING', 'One of the Bay', 'One of the Bay'

    UNION SELECT 10, '0000256', 'MEPG', 'STAGING', 'One of the Bay', 'One of the Bay'

    UNION SELECT 10, '0000256', 'MEPG', 'STAGING', 'One of the Bay', 'One of the Bay'

    CREATE TABLE #TempOutput(org INT NOT NULL,

     acct VARCHAR(7) NOT NULL,

     class VARCHAR(4) NOT NULL,

     prod VARCHAR(7) NOT NULL,

     note VARCHAR(16) NOT NULL,

     htmltext VARCHAR(16) NOT NULL,

     PRIMARY KEY (org, acct, class, prod)

    )

    INSERT INTO #TempOutput (org, acct, class, prod, note, htmltext)

    SELECT org, acct, class, prod, MAX(note), MAX(htmltext)

    FROM #Temp

    GROUP BY org, acct, class, prod

    SELECT *

    FROM #TempOutput

    DROP TABLE #Temp

    DROP TABLE #TempOutput

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply