Finding duplicates + an extra column

Question

Post reply

Finding duplicates + an extra column

Svante

Valued Member

Points: 73
More actions
May 19, 2010 at 6:39 am

#221776

Hi, I am stuck with something I think is really simple to solve, but...
In addition to the duplicates, I am also interested to see the value of a column not in the unique list, in my example, col1, col2 and col3
is the key, and col4 not, but I would also see the value of col4 where there are duplicates, anyone there who please could help me?
SELECT col1, col2, col3, col4 FROM table
GROUP BY col1, col2, col3
HAVING COUNT(*) > 1

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

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

Removed

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

You might want to look at using a CTE such as:

;with numbered as(SELECT rowno=row_number() over

(Partition by COL1, COL2,COL3 order by COL1),COL1,COL2, COL3,COL4 FROM #TableX)

SELECT COL1, COL2, COL3, COL4 from numbered

WHERE Rowno > 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 3

Also:

DECLARE @Table

TABLE (

col1 INTEGER NOT NULL,

col2 INTEGER NOT NULL,

col3 INTEGER NOT NULL,

col4 INTEGER NOT NULL

);

INSERT @Table (col1, col2, col3, col4) VALUES (1, 2, 3, 4);

INSERT @Table (col1, col2, col3, col4) VALUES (1, 2, 3, 5);

INSERT @Table (col1, col2, col3, col4) VALUES (1, 2, 3, 6);

INSERT @Table (col1, col2, col3, col4) VALUES (1, 2, 3, 7);

INSERT @Table (col1, col2, col3, col4) VALUES (4, 5, 6, 0);

INSERT @Table (col1, col2, col3, col4) VALUES (7, 8, 9, 1);

INSERT @Table (col1, col2, col3, col4) VALUES (7, 8, 9, 2);

INSERT @Table (col1, col2, col3, col4) VALUES (7, 8, 9, 3);

SELECT T1.col1, T1.col2, T1.col3, iTVF2.col4_values

FROM (

SELECT col1, col2, col3

FROM @Table

GROUP BY col1, col2, col3

HAVING COUNT(*) > 1

) T1

CROSS

APPLY (

SELECT ',' + CONVERT(VARCHAR(20), T2.col4)

FROM @Table T2

WHERE T2.col1 = T1.col1

AND T2.col2 = T2.col2

AND T2.col3 = T2.col3

FOR XML PATH('')

) iTVF1 (col4_csv)

CROSS

APPLY (

SELECT STUFF(iTVF1.col4_csv, 1, 1, SPACE(0))

) iTVF2 (col4_values);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi