February 19, 2009 at 2:27 pm
I have history table which I need to clear the duplicate records.Everymonth I used to copy whole data to history with version
and monthid.
Now I need to clear the duplicates from the history
If I eliminate two feilds (version and monthid.)
I can get the nonduplicate records
But my problem is how to add the version and monthid to the nonduplicate records which I got
or how to get the correct values..?please help
key month id version col1 col2 col3 col4
q1 jan 1 zz xx cc vv
q1 feb 1 zz xx cc vv
q1 mar 1 zz xx cc c
q1 april 1 zz xx cc c
q1 april 2 zz xx wer c
q2 jan 1 ww sa cc vv
q2 feb 1 ww sa cc vv
q2 mar 1 ww xx cc c
q2 mar 2 ww xx cc d
q2 april 1 ww xx cc d
results should be
q1 jan 1 zz xx cc vv
q1 mar 1 zz xx cc c
q1 april 2 zz xx wer c
q2 jan 1 ww sa cc vv
q2 mar 1 ww xx cc c
q2 mar 2 ww xx cc d
q2 april 1 ww xx cc d
February 19, 2009 at 3:16 pm
Please try:
DECLARE @test-2 TABLE( VARCHAR(20)
,monthID VARCHAR(20)
,[version] VARCHAR(20)
,col1 VARCHAR(20)
,col2 VARCHAR(20)
,col3 VARCHAR(20)
,col4 VARCHAR(20))
INSERT INTO @test-2
SELECT 'q1','jan','1','zz','xx','cc','vv' UNION ALL
SELECT 'q1','feb','1','zz','xx','cc','vv' UNION ALL
SELECT 'q1','mar','1','zz','xx','cc','c' UNION ALL
SELECT 'q1','april','1','zz','xx','cc','c' UNION ALL
SELECT 'q1','april','2','zz','xx','wer','c' UNION ALL
SELECT 'q2','jan','1','ww','sa','cc','vv' UNION ALL
SELECT 'q2','feb','1','ww','sa','cc','vv' UNION ALL
SELECT 'q2','mar','1','ww','xx','cc','c' UNION ALL
SELECT 'q2','mar','2','ww','xx','cc','d' UNION ALL
SELECT 'q2','april','1','ww','xx','cc','d'
; WITH TESTCte AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY ,[version]
,col1
,col2
,col3, col4 ORDER BY , CASE monthID
WHEN 'Jan' THEN 1
WHEN 'Feb' THEN 2
WHEN 'Mar' THEN 3
WHEN 'April' THEN 4 --need to add all other months
ELSE 12 END) AS RowNumber
FROM @test-2)
DELETE FROM TESTCte WHERE RowNumber > 1
SELECT * FROM @test-2
February 19, 2009 at 3:29 pm
[font="Verdana"]Milla, you love your row_number()! 😀
Another possibility is to use a group by and use max(), sum(), etc.
[/font]
February 19, 2009 at 3:30 pm
Here are 2 different ways. Both use the table variable @monthMap
The 2nd method is pretty much the same as Milla's
declare @monthMap TABLE (
id int NOT NULL,
code char(8) NOT NULL
)
INSERT @monthMap
SELECT 1, 'jan' UNION ALL
SELECT 2, 'feb' UNION ALL
SELECT 3, 'mar' UNION ALL
SELECT 4, 'april' UNION ALL
SELECT 5, 'may' UNION ALL
SELECT 6, 'june' UNION ALL
SELECT 7, 'july' UNION ALL
SELECT 8, 'aug' UNION ALL
SELECT 9, 'sept' UNION ALL
SELECT 10, 'oct' UNION ALL
SELECT 11, 'nov' UNION ALL
SELECT 12, 'dec'
SELECT
X.,
monthid = (SELECT code FROM @monthMap WHERE (id = CONVERT(int, SUBSTRING(X.bits, 1, 4)))),
version = CONVERT(int, SUBSTRING(X.bits, 5, 4)),
X.col1,
X.col2,
X.col3,
X.col4
FROM (
SELECT H., H.col1, H.col2, H.col3, H.col4,
bits = MIN(CONVERT(BINARY(4), M.id) + CONVERT(BINARY(4), H.version))
FROM dbo.history H
JOIN @monthMap M ON (H.monthid = M.code)
GROUP BY H., H.col1, H.col2, H.col3, H.col4
) X
ORDER BY X., X.bits
;WITH cteGrp AS (
SELECT H., H.monthid, H.version, H.col1, H.col2, H.col3, H.col4,
rn = ROW_NUMBER() OVER (PARTITION BY H., H.col1, H.col2, H.col3, H.col4 ORDER BY M.id, H.version),
mn = M.id
FROM dbo.history H
JOIN @monthMap M ON (H.monthid = M.code)
)
SELECT G., G.monthid, G.version, G.col1, G.col2, G.col3, G.col4
FROM cteGrp G
WHERE (G.rn = 1)
ORDER BY G., G.mn, G.version
February 20, 2009 at 10:36 am
Thanks guys...It Works
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply