?
Solved

query to select duplicated rows?

Posted on 2004-11-15
14
Medium Priority
?
240 Views
Last Modified: 2011-10-03
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
Comment
Question by:qinyan
  • 9
  • 5
14 Comments
 
LVL 4

Expert Comment

by:strangelynormal1
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

by:
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

by:qinyan
ID: 12596757
Yes I did the same thing but how can I get all the duplicated rows in the resultset?
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 4

Expert Comment

by:strangelynormal1
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

by:strangelynormal1
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

by:strangelynormal1
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

by:qinyan
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

by:strangelynormal1
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

by:qinyan
ID: 12597828
Yeah yeah right, Sorry I mean the second query.
0
 
LVL 4

Expert Comment

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

Author Comment

by:qinyan
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

by:strangelynormal1
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

by:qinyan
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

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question