Syntax to find all the redundancies in a table

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

General SQL Server Forums

New to SQL Server Programming

Syntax to find all the redundancies in a table

Author

Topic

msvdm
Starting Member

17 Posts

Posted - 2011-07-28 : 10:53:56

What query determines the entire redundancies present in the table.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 10:57:39

This will show the list of col1 and col2 combination that exist more than once in your table.

select
  col1,
  col2
from
  YourTable
group by
  col1,
  col2
having
  count(*) > 1

msvdm
Starting Member

17 Posts

Posted - 2011-07-28 : 11:15:40

Thanks for your valuable reply

I want to know the redundancies of all the columns in the table. When I select all the columns and include them in the group by I am not able to see or get the data if I use the same syntax as above.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-28 : 11:21:35

quote:
Originally posted by msvdm

What query determines the entire redundancies present in the table.

Since you didn't provide your table structure, sample data or expected output it's hard to tell what you mean by "entire redundancies." Perhaps this link will help you to prove your DDL, DML and expected out so we can better answer your question:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

msvdm
Starting Member

17 Posts

Posted - 2011-07-28 : 11:25:54

Thanks for your valuable reply

select
col1,col2,.......col8
from
Mytable
groupby
col1,col2,.....col8
having
count(*)>1

msvdm
Starting Member

17 Posts

Posted - 2011-07-28 : 11:56:28

I am unable to see the data when I do the following

select
TyeId,YId,Rate,IndicId,Ra,RolId,Approv,PpId
group by
TyeId,YId,Rate,IndicId,Ra,RolId,Approv,PpId
having
count(*) > 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 12:26:19

quote:
Originally posted by msvdm

I am unable to see the data when I do the following

select
TyeId,YId,Rate,IndicId,Ra,RolId,Approv,PpId
group by
TyeId,YId,Rate,IndicId,Ra,RolId,Approv,PpId
having
count(*) > 1

The query is going to look for the combination of all 8 columns to see if there are any that are exactly identical. If there are none, you will not get any rows in the result. This example might help - here, I am looking for unique combination of col1 and col2. There are 2 such combinations:

CREATE TABLE #tmp (col1 INT, col2 INT, col3 INT);
INSERT INTO #tmp 
SELECT 1,1,1 UNION ALL
SELECT 1,1,2 UNION ALL
SELECT 1,2,1 UNION ALL
SELECT 2,1,1 UNION ALL
SELECT NULL,1,1 UNION ALL
SELECT NULL,1,3;

SELECT 
	col1,
	col2
FROM
	#tmp
GROUP BY
	col1,
	col2
HAVING
	COUNT(*) > 1
	
DROP TABLE #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources