Solved

# query to select duplicated rows?

Posted on 2004-11-15
Medium Priority
240 Views
Is there a way to select all those dulicated rows? If in a table there are multiple rows with exact the same record, how to select those rows? Thanks!
0
Question by:qinyan
• 9
• 5

LVL 4

Expert Comment

ID: 12588944
SELECT b.*
FROM   (SELECT field1
,field2
...
,fieldn
FROM   Table
GROUP BY field1
,field2
...
,fieldn
HAVING COUNT(*)>1)   a
,Table                            b
WHERE a.field1  = b.field1
AND      a.field2  = b.field2
...
AND      a.fieldn  = b.fieldn
0

LVL 4

Accepted Solution

strangelynormal1 earned 375 total points
ID: 12588956
Sorry...I think this may be better:

SELECT field1
,field2
...
,fieldn
,COUNT(*)
FROM   Table
GROUP BY field1
,field2
...
,fieldn
HAVING COUNT(*)>1

Then you can see how many times each one occurred easier...
0

Author Comment

ID: 12596757
Yes I did the same thing but how can I get all the duplicated rows in the resultset?
0

LVL 4

Expert Comment

ID: 12597597
Sorry to be dense, but in what way is the first query not getting the duplicated rows in the resultset?
0

LVL 4

Expert Comment

ID: 12597635
Uh...if you wanted to eliminate dups, the fastest way is to:

SELECT DISTINCT *
INTO #temp
FROM   Table

truncate Table

INSERT Table
SELECT *
FROM #temp
0

LVL 4

Expert Comment

ID: 12597647
I forgot a 'table'...so I'm going to change the table name to Table1:

SELECT DISTINCT *
INTO #temp
FROM   Table1

truncate table Table1

INSERT Table1
SELECT *
FROM #temp
0

Author Comment

ID: 12597794
Ok, from the first query it returns data like this:

Product      cost    price       date                   count
123            1.2      1.5        2004/10/01           2
124            1.5      2.0        2004/10/01           3

what i like to see in the result is like this:

Product    cost       price         date
123          1.2         1.5         2004/10/01
123          1.2         1.5         2004/10/01
124          1.5         2.0         2004/10/01
124          1.5         2.0         2004/10/01
124          1.5         2.0         2004/10/01

so you only see products with duplicated records and you can run through the list to have a quick check.
Those products without duplication will not show. I think we need one more step: select products from the original table which are not in the resultset returned from the above query.
Thanks anyway!
0

LVL 4

Expert Comment

ID: 12597816
I'm sorry...that is the second query...here's the first query:
SELECT b.*
FROM   (SELECT field1
,field2
...
,fieldn
FROM   Table
GROUP BY field1
,field2
...
,fieldn
HAVING COUNT(*)>1)   a
,Table                            b
WHERE a.field1  = b.field1
AND      a.field2  = b.field2
...
AND      a.fieldn  = b.fieldn
0

Author Comment

ID: 12597828
Yeah yeah right, Sorry I mean the second query.
0

LVL 4

Expert Comment

ID: 12597847
Uh...so you're looking for records that aren't duplicated?
0

Author Comment

ID: 12597890
No I'm looking for records that ARE duplicated. Your second query did work but we just need one more step to get what we need. Anyway your second answer has been accepted. Thanks!
0

LVL 4

Expert Comment

ID: 12598860
Okay...I think the first answer may have suited your needs better...but I'm glad the second one worked out anyways.
0

Author Comment

ID: 12599029
I'm sorry. All I looked at was the second query I didn't even try the first one. That's where all the confusion came from.
0

LVL 4

Expert Comment

ID: 12600123
Hehe...okay...not a big deal.  ;)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month13 days, 20 hours left to enroll