query help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2007-05-15 : 11:20:09

how would I go around to solve this:

Select cl1, cl2 from tbl
where ???
group by cl1, cl2
having count(cl1) > 1
order by cl1

I want the cl2 to return if it have more then 2 result, and match cl1

thanks

matty1stop
Starting Member

37 Posts

Posted - 2007-05-15 : 11:46:55

I'm having a little trouble understanding the results that you would like. Could you provide some sample data and the results that you would expect from the query?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-15 : 11:56:09

What do you mean by "..match cl1" ?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-15 : 11:58:11

quote:
Originally posted by hai

how would I go around to solve this:

Select cl1, cl2 from tbl
where ???
group by cl1, cl2
having count(cl1) > 1
order by cl1

I want the cl2 to return if it have more then 2 result, and match cl1

thanks

match = equal ?


Select cl1, cl2 
from   tbl
where  c11 = c12
group by cl1, cl2
having count(*) > 1

hai
Yak Posting Veteran

84 Posts

Posted - 2007-05-15 : 11:59:13

sorry,

if table1 have the following
col1-----col2
12345 BCN
12345 AXC
12345 CHY
23457 BCN

I want to get the all result of col2 with the matching col1 (12345) or col1 count > 1.

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-15 : 12:03:02

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

hai
Yak Posting Veteran

84 Posts

Posted - 2007-05-15 : 12:21:03

Hi Khtan,

if I want to filter out the select statment, distinct col2. how would I do that? the reason for this is that the query return the same duplicate value, which I want to filter out.

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-15 : 21:46:31

Tried using the distinct statement ?

Not quite understand what do you want here. Please post the table DDL, sample data and expected result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources