query help... 1

[tran008]

hi all,

I a bit stuck with this problem. I trying to query the result if col1 have more then 1 and col2 is not the same, retrieve the fields. eg:

table:
col1--col2
1-----a
1-----b
1-----c
1-----c
2-----a
2-----b
2-----b
3-----a

result want:
1,a,b,c
2,a,b

thanks

 

[AlexCuse]

thread183-1159740

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[tran008]

Hi Alex,

thank for the link, but I'm looking for something. I just want to compare col1 to col2 to meet these condition. If col1 is the same, and col2 is the same, don't return. Return only if col2 are difference.

eg:
col col2
1 A
1 B
2 a
2 a

Display col1 1 only because col2 is different. I was looking into Max and Min function but getting no where.


thanks

 

[AlexCuse]

Well, maybe you should not have shown a concatenated result in your desired results then ;-)

Try this:

select distinct
col1
, col2
from leTable

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[tran008]

Alex,

1 other condition I need to add is that count(col1) > 1.

thanks

 

[AlexCuse]

Ah. You need to join to a subquery (I think).

like this:

[COLOR=green]--creat test data
[/color][COLOR=blue]declare[/color] @leTable [COLOR=blue]table[/color] (col1 [COLOR=blue]int[/color], col2 [COLOR=blue]char[/color](1))

[COLOR=blue]insert[/color] @leTable
[COLOR=blue]select[/color] 1, [COLOR=red]'a'[/color]
union all [COLOR=blue]select[/color] 1, [COLOR=red]'b'[/color]
union all [COLOR=blue]select[/color] 1, [COLOR=red]'a'[/color]
union all [COLOR=blue]select[/color] 2, [COLOR=red]'b'[/color]
union all [COLOR=blue]select[/color] 3, [COLOR=red]'a'[/color]
union all [COLOR=blue]select[/color] 3, [COLOR=red]'a'[/color]
union all [COLOR=blue]select[/color] 3, [COLOR=red]'b'[/color]


[COLOR=green]--query will join to a subquery containing only the col1 
[/color][COLOR=green]--values that appear >1 time to limit the rows returned
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color]
a.col1
, a.col2
[COLOR=blue]from[/color] @leTable a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] col1
[COLOR=blue]from[/color] @leTable
[COLOR=blue]group[/color] [COLOR=blue]by[/color] col1
[COLOR=blue]having[/color] [COLOR=#FF00FF]count[/color](col1) > 1
) b
[COLOR=blue]on[/color] a.col1 = b.col1
[COLOR=blue]order[/color] [COLOR=blue]by[/color] a.col1

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom