query to select duplicated rows?

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!
qinyanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qinyanAuthor Commented:
Yes I did the same thing but how can I get all the duplicated rows in the resultset?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

strangelynormal1Commented:
Sorry to be dense, but in what way is the first query not getting the duplicated rows in the resultset?
0
strangelynormal1Commented:
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
strangelynormal1Commented:
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
qinyanAuthor Commented:
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
strangelynormal1Commented:
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
qinyanAuthor Commented:
Yeah yeah right, Sorry I mean the second query.
0
strangelynormal1Commented:
Uh...so you're looking for records that aren't duplicated?
0
qinyanAuthor Commented:
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
strangelynormal1Commented:
Okay...I think the first answer may have suited your needs better...but I'm glad the second one worked out anyways.
0
qinyanAuthor Commented:
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
strangelynormal1Commented:
Hehe...okay...not a big deal.  ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.