Doublet, triplet - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Doublet, triplet

Author

Topic

Lynx
Starting Member

4 Posts

Posted - 2010-12-02 : 14:04:46

Hi,

I am trying to count doublet, triplet...
I have made a script that count them but it also counts the singles.

Is it possible to only count doublet, triplet?

Thanks in advance!
Carl

Result before
col1 col2 col3 doublet id
1111 ab 100 0 1
1112 ca 200 0 2
1112 ca 200 0 3
1112 ca 200 0 4
1115 ba 50 0 5

Result after
col1 col2 col3 doublet id
1111 ab 100 1 1
1112 ca 200 1 2
1112 ca 200 2 3
1112 ca 200 3 4
1115 ba 50 1 5

Result I wish
col1 col2 col3 doublet id
1111 ab 100 0 1
1112 ca 200 1 2
1112 ca 200 2 3
1112 ca 200 3 4
1115 ba 50 0 5

My script:
CREATE TABLE #TempDoublet(
[col1] [varchar](20) NULL,
[col2] [varchar](20) NULL,
[col3] [varchar](20) NULL,
[doublet] [int] NULL,
[id] [int] NOT NULL
)

CREATE CLUSTERED INDEX IX_1 ON #TempDoublet (col1, col2, col3)

INSERT INTO #TempDoublet(col1, col2, col3, id)
SELECT col1, col2, col3, id
FROM Table1

DECLARE @var1 INT;
DECLARE @col1 VARCHAR(20);
DECLARE @col2 VARCHAR(20);
DECLARE @col3 VARCHAR(20);
SELECT @var1 = 0;

UPDATE #TempDoublet
SET @var1 = CASE
WHEN @col1 = col1 AND
@col2 = col2 AND
@col3 = col3 THEN @var1 + 1
ELSE 1
END,
@col1 = col1,
@col2 = col2,
@col3 = col3,
doublet = CASE
WHEN doublet = 0 OR doublet IS NULL THEN @var1
ELSE doublet
END

UPDATE Table1
SET doublet = t.doublet
FROM Table1 AS o JOIN #TempDoublet AS t ON
o.id = t.id

DROP TABLE #TempDoublet

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 15:36:57

something like

;with cte as
(
select id, col1, col2, col3, seq = row_number() over (partition by col1,col2,col3 order by id)
)
update Table1
set doublet = cte.seq
from Table1 t
join cte
on t.id = cte.id
join (select col1,col2,col3 from cte where seq = 2) a
on a.col1 = t.col1
and a.col2 = t.col2
and a.col3 = t.col3

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lynx
Starting Member

4 Posts

Posted - 2010-12-05 : 07:06:09

Hi,

Your solution is beautiful.
I completed your solution with "from Table1" in the CTE and then it worked great.

Thank you so much, you saved my day!
Carl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources