Multiple DISTINCTS?

Hello,
Is it possible to constrain a query so that I receive only the duplicate rows (all column data matches)? For example:
SELECT DISTINCT COL1,
DISTINCT COL2,DISTINCT COL3
FROM TABLE
I did a search on multiple DISTINCTS but came up with nothing. Thanks in advance.DISTINCT applys to the whole row of the result set, not individual columns of it. When you do aSELECT DISTINCT col1, col2, col3
FROM myTableyou will get one row for any given combination of col1, col2, and col3. You can use DISTINCT within aggragate functions to get aggrigates of the unique values, but when applied against a result set, DISTINCT applies to the whole set (row).

-PatP|||uh, huh?

SELECT DISTINCT Col1, Col2, Col3

Will give you 1 unique row..

You want to only see dups?

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1

Is that what your after?|||Originally posted by Brett Kaiser
uh, huh?

SELECT DISTINCT Col1, Col2, Col3

Will give you 1 unique row..

You want to only see dups?

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1

Is that what your after?

------------------

Thanks for your suggestions. I am looking for a resultset of:

COL1 COL2 COL3
---------
brown tall dog
brown tall dog
brown tall dog

Thanks again for your help.|||Did the code give you what you wanted...I'm not sure...|||Originally posted by Brett Kaiser
Did the code give you what you wanted...I'm not sure...

Not yet, but I could be doing something wrong.

I've tried the following:
SELECT DISTINCT COL1, COL2, COL3
FROM myTABLE

and:
SELECT COL1, COL2, COL3
FROM myTABLE
GROUP BY COL1, COL2, COL3

and:
SELECT DISTINCT COL1, COL2, COL3
FROM myTABLE
HAVING COUNT(*) > 1

and I've tried:
SELECT COL1, COL2, COL3
FROM myTABLE
GROUP BY COL1, COL2, COL3
HAVING COUNT(*) > 1|||Well that should work...

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(25),Col2 varchar(25),Col3 varchar(25))
GO

INSERT INTO myTable99(Col1,Col2,Col3)
select 'Brown','Tall','Dog' UNION ALL
select 'Brown','Tall','Dog' UNION ALL
select 'Brown','Tall','Dog' UNION ALL
select 'Blonde','Small','Pussy cat' UNION ALL
select 'Red','Medium','Snapper Turtle'
GO

SELECT Col1, Col2, Col3
FROM myTable99
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1
GO

DROP TABLE myTable99
GO

Oh, are you looking for all three?|||SELECT * FROM myTable99 o WHERE EXISTS(
SELECT *
FROM myTable99 i
WHERE o.Col1 = i.Col1
AND o.Col2 = i.Col2
AND o.Col3 = i.Col3
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1)
GO

All three...|||Brett,
That should do it! Combined with the info in the links provided in this thread: http://www.dbforums.com/t991775.html

I should be able to put something together. I really do appreciate all your hard work.

Regards,
Americus Johnson

Thanks also to Pat!|||Hard Work...Lord no...

That's why I became a dba...

:D

PS Don't forget to take ALL of your animal freinds...ya might get lucky|||Originally posted by Brett Kaiser
Hard Work...Lord no...

That's why I became a dba...

:D

PS Don't forget to take ALL of your animal freinds...ya might get lucky

True dat.|||yup