Selecting only rows that same the value in one column

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

Selecting only rows that same the value in one column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-05 : 10:03:29

Mike writes "Hi,
I am a beginner with TSQL and I hope this is not a silly question :-)

Lets say I have a table with 2 columns, 1 a primary key identity field with increment 1 and the other a char.

EG:

ID ANIMAL
---------
1 CAT
2 DOG
3 PIG
4 RAT
5 PIG
6 DOG
7 DOG
.
.
And so on with many entries

How do I return a selection of rows that have the contents of the ANIMAL field matching 1 or more times

EG:
From above table I want to return rows 2,6 & 7 and 3 & 5 ONLY and not 1(CAT) and 4(RAT) because they only occur once.

In my real life situation I have unknown numeric data in field 2 but the principal is the same.

How do I do this?

Thanks in Advance
Mike"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-05 : 10:21:40

[code]
select *
from tbl t inner join
(
select ANIMAL
from tbl
group by ANIMAL
having count(*) > 1
) a
on t.ANIMAL = a.ANIMAL
[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-06 : 07:56:18

Or...

--data
declare @t table (ID int, ANIMAL varchar(10))
insert @t
          select 1, 'CAT'
union all select 2, 'DOG'
union all select 3, 'PIG'
union all select 4, 'RAT'
union all select 5, 'PIG'
union all select 6, 'DOG'
union all select 7, 'DOG'

--calculation
select * from @t a where (select count(*) from @t where animal = a.animal) > 1

/*results
ID          ANIMAL     
----------- ---------- 
2           DOG
3           PIG
5           PIG
6           DOG
7           DOG
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources