June 12, 2006 at 1:06 pm
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.
June 12, 2006 at 1:16 pm
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.
June 12, 2006 at 2:06 pm
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
June 12, 2006 at 2:16 pm
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