Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding duplicates + an extra column Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2010 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 14, 2011 6:03 AM
Points: 1, Visits: 4
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
Post #924241
Posted Wednesday, May 19, 2010 9:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 9:34 AM
Points: 1,221, Visits: 1,297
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/
Post #924408
Posted Wednesday, May 19, 2010 9:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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

Before posting a performance problem please read
Post #924412
Posted Wednesday, May 19, 2010 10:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 9,928, Visits: 11,196
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #924805
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse