looping - SQL Server Forums

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

looping

Author

Topic

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-21 : 08:17:28

hi guys,

I have a table like this,
S D C

Bi Ar Ar
Bi Ar Ch
Bi Ar Ma
Bi Au Ar
Bi Au Ch
Bi Au Ma
As Ar Ar
As Ar Ch
As Ar Ma
As Au Ar
As Au Ch
As Au Ma
As Au Ma

I have 3 columns S, D, C. i have text values in it. I need to write a query such that it will check each row for distinct value.For ex, all the rows are distinct except the last one. so i need to see all the duplicate entries. can anyone help me?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 08:22:35

[code]Select S, D, C
from Tbl
Group by S, D, C
having count(*)>1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 09:12:47

I'm bored...

-- Prepare sample data
DECLARE	@Sample TABLE (S VARCHAR(2), D VARCHAR(2), C VARCHAR(2))

INSERT	@Sample
SELECT	'Bi', 'Ar', 'Ar' UNION ALL
SELECT	'Bi', 'Ar', 'Ch' UNION ALL
SELECT	'Bi', 'Ar', 'Ma' UNION ALL
SELECT	'Bi', 'Au', 'Ar' UNION ALL
SELECT	'Bi', 'Au', 'Ch' UNION ALL
SELECT	'Bi', 'Au', 'Ma' UNION ALL
SELECT	'As', 'Ar', 'Ar' UNION ALL
SELECT	'As', 'Ar', 'Ma' UNION ALL
SELECT	'As', 'Au', 'Ar' UNION ALL
SELECT	'As', 'Au', 'Ch' UNION ALL
SELECT	'As', 'Au', 'Ma' UNION ALL
SELECT	'As', 'Au', 'Ma'

-- Duplicates
SELECT		S,
		D,
		C,
		COUNT(*) AS Instances
FROM		@Sample
GROUP BY	S,
		D,
		C
HAVING		COUNT(*) > 1

-- Missing combinations
SELECT DISTINCT	s.S,
		d.D,
		c.C
FROM		@Sample AS s
CROSS JOIN	@Sample AS d
CROSS JOIN	@Sample AS c
LEFT JOIN	@Sample AS o ON o.S = s.S AND o.D = d.D AND o.C = c.C
WHERE		o.S IS NULL

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-21 : 09:19:16

What? You buried Paul?

SELECT * FROM table WHERE EXISTS (SELECT S,D,C FROM Table...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-21 : 09:53:39

thnx harsh. it worked. thnx to others too for replying.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources