sql novice: how to find only duplicates

[PJ Arends]

SELECT Col1, Col2, Col3 FROM MyTable ORDER BY Col1, Col2, Col3;

6582 row(s) affected

SELECT DISTINCT Col1, Col2, Col3 FROM MyTable ORDER BY Col1, Col2, Col3;

6473 rows(s) affected How can I get just the 109 rows that are duplicates? ie: the opposite of DISTINCT? Thanks

---

You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!

[Blue_Boy]

Try to union all by three selects like:

select distinct col1 from mytable
union all
select distinct col2 from mytable
union all
select distinct col3 from mytable

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[J4amieC]

You can do this using a Group By clause

SELECT Col1,Col2,Col3, COUNT(*) as DuplicateCount
FROM MyTable
GROUP BY Col1,Col2,Col3
ORDER BY DuplicateCount DESC

At this point you will have all the records, with the duplicates (Those with a count>1) at the beginning of the list. Interestingly, you can reduce this to JUST the duplicates by replacing the ORDER BY with a HAVING clause

SELECT Col1,Col2,Col3, COUNT(*) as DuplicateCount
FROM MyTable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1

Now you only have the duplicates.

[Mycroft Holmes]

You're next question will be how do I remove the duplicates? Look into row_number() and partition over in BOL

Never underestimate the power of human stupidity RAH

[PJ Arends]

Thanks for the help, but unfortunately this does not give me the results I am looking for.

---

You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!

[PJ Arends]

Thanks, this is exactly what I am looking for.

---

You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!

[PJ Arends]

Actually not. Using the results I got using J4amieC's answer I am going to try and figure out which other column in the table will make these rows unique.

---

You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!