finding col1 duplicate values per each col2 value

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 2008 Forums

Transact-SQL (2008)

finding col1 duplicate values per each col2 value

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2011-06-07 : 11:48:18

Hello,

I need some help with this query.

I have a table like this:


Col1       Col2
----       ----
1000       753
1001       395
1010       3
2010       39
..         ..
8019       739

Now, I don't know if there is a 1:1 relationship these values for Col1:Col2, as there are more columns and over a million records in the real table, but I'd like to find out. What I'd like to do is for each value in Col1, find all distinct Col2 values.

Here is the current query (not correct at all) that needs to be modified:


select col1, col2, count(col2) Col2_Tot_per_Col1
from ex_tbl
group by col1, col2
having COUNT(col2) > 1
order by col1

As an example, for the dataset below:


Col1       Col2
----       ----
1000       753
1000       753
1001       395
1001       395
1002       79
1003       34
1004       56
1004       100

I'd like the query to return:


Col1       Col2     Col2_Tot_per_Col1     
----       ----     -----------------
1000       753      1
1001       395      1
1002       79       1
1003       34       1
1004       56       2
1004       100      2

Or, at the very least:


Col1       Col2_Tot_per_Col1     
----       -----------------
1000       1
1001       1
1002       1
1003       1
1004       2

Thank you in advance.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-07 : 13:05:08

How about something like this:


Declare @t table (col1 int, col2 int)

Insert Into @t 
Select 1, 4 Union All	-- not 1:1
Select 1, 5 Union All

Select 2, 2 Union All	-- 1:1

Select 3, 4 Union All	-- 1:1
Select 3, 4 Union All

Select 4, 8 Union All	-- not 1:1
Select 4, 1 Union All

Select 5, 43			-- 1:1



Select * 
From 
	(
	Select 
		col1, 
		col2, 
		Col2_Tot_per_Col1 = count(col2) Over(Partition By col1) 
	From @t
	Group By col1, col2
	) A
Where Col2_Tot_per_Col1 > 1
Order By col1

Corey

I Has Returned!!

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2011-06-07 : 15:26:55

Perfect! Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources