I want to choose the NOT distinct records.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

I want to choose the NOT distinct records.

Author

Topic

Faiyth
Starting Member

19 Posts

Posted - 2003-02-19 : 12:10:25

Here's the table.. and below is what I want to do. Any help would be appreciated.

TABLE:
Area (column 1)
-----
Oregon
California
California
California
Oregon
Washington
Idaho
Washington

I want ALL the cities that are duplicates to be returned. Not the distinct ones. Just the duplicates. Is there a select statement that lets me return only duplicates?

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-19 : 12:13:40

SELECT AREA
FROM TABLE
GROUP BY AREA
HAVING COUNT(*) > 1

Hope this Helps

Brett

8-)

Faiyth
Starting Member

19 Posts

Posted - 2003-02-19 : 12:37:19

No that only returns 1 of each that has duplicates. I want ALL of the duplicates to be returned.

If there are 4 records with area "California" then I want 4 records with California returned.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-19 : 12:43:35

OK, but I'm kind of confused. You want to see "California" 4 times? Why not:

"California","4"

The above is done with:

SELECT AREA, Count(*)
FROM TABLE
GROUP BY AREA
HAVING COUNT(*) > 1

However you can get what you want by:

SELECT * From Table o WHERE EXISTS (
SELECT AREA FROM Table i
WHERE o.AREA = i.AREA
GROUP BY AREA
HAVING COUNT(*) > 1)

Hope this helps

Brett

8-)

ps what are you ultimatley trying to accomplish?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-19 : 12:44:46

SELECT AREA
FROM TABLE T1
WHERE (SELECT COUNT(*) FROM Table T2 WHERE T1.Area = T2.Area) > 1

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-19 : 13:12:26

Ya Learn something new EVERY day...I didn't think your SQL would work, Jeff...but sure enough it does...don't undestand why though. It would appear that the predicate (SELECT Count(*) From table)...should only produce 1 row! But because of the correlation it does a count per value? In essences it's forcing a Grouping?????? I see it, but don't beleive it.

Anyway, Here's my test to prove that it does work (any explanation as to why?)

Brett

8-)

Create Table Area_Table (Area varchar(255))
Go
Insert Into Area_Table (Area) Values ('Alaska')
Go
Insert Into Area_Table (Area) Values ('California')
Go
Insert Into Area_Table (Area) Values ('California')
Go
Insert Into Area_Table (Area) Values ('California')
Go
Insert Into Area_Table (Area) Values ('California')
Go
Insert Into Area_Table (Area) Values ('Delaware')
Go
Select * From Area_Table
Go
SELECT AREA
FROM Area_Table T1
WHERE (SELECT COUNT(*) FROM Area_Table T2 WHERE T1.Area = T2.Area) > 1
Go
Drop Table Area_Table
Go

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-19 : 19:37:51

I haven't run this query, but it looks like it selects all Records (distinct and duplicate records) that have a duplicate.

If you're looking for a set of duplicates where one of the distinct California rows is not included, then you'll need something like:

[url]http://sqlteam.com/item.asp?ItemID=3331[/url]

This article focuses on deleting the duplicates, but the approach is the same for Selecting duplicates.

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-19 : 22:06:23

quote:

I haven't run this query, but it looks like it selects all Records (distinct and duplicate records) that have a duplicate.

If you're looking for a set of duplicates where one of the distinct California rows is not included, then you'll .....

Sam, I'm afraid I'm not sure what you mean; both sentences above confuse me. Can you explain a little further?

Thanks!
(I may just be getting sleepy but I've re-read the post a few times and it's just not clicking to me what you are saying)

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-19 : 22:41:31

Hi Jeff,

I didn't read the post that clarified all records of any Area with duplicates were wanted.

Sam

Edited by - SamC on 02/20/2003 00:28:43

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources