need help with a query

19 May 2006 7:29 PM

Hassan

Create table test
(col1 varchar(50),col2 int)

insert test values ('ABC',1)
insert test values ('ABC',2)
insert test values ('XYZ',1)
insert test values ('XYZ',1)

I would like the output to report those unique values in Col1 that have
different values in Col2.. So the output based upon the input in table test
would return
'ABC'

19 May 2006 7:38 PM

Jim Underwood

something like this...

select a.col1
from test a
where exists
(
   select 1
   from test b
   where b.col1 = a.col1
   and b.col2 <> a.col2
)

Show quote

"Hassan" <Has***@hotmail.com> wrote in message
news:OIYlOq3eGHA.4932@TK2MSFTNGP03.phx.gbl...
> Create table test
> (col1 varchar(50),col2 int)
>
> insert test values ('ABC',1)
> insert test values ('ABC',2)
> insert test values ('XYZ',1)
> insert test values ('XYZ',1)
>
>
> I would like the output to report those unique values in Col1 that have
> different values in Col2.. So the output based upon the input in table
test
> would return
> 'ABC'
>
>

19 May 2006 10:47 PM

--CELKO--

SELECT col1
FROM CrappyNonTableWithoutKey
GROUP BY col1
HAVING MIN(col2) < MAX(col2) ;

20 May 2006 7:12 AM

Tony Rogerson

Should that not be

HAVING MIN(col2) <> MAX(col2) ;

They are looking for values where col2 are different.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1148078875.868716.172500@j33g2000cwa.googlegroups.com...
> SELECT col1
> FROM CrappyNonTableWithoutKey
> GROUP BY col1
> HAVING MIN(col2) < MAX(col2) ;
>

20 May 2006 3:34 PM

--CELKO--

Think about it for a second:

HAVING MIN(col2) <> MAX(col2) ;

is defined as:

HAVING (MIN(col2) < MAX(col2)) OR (MIN(col2) > MAX(col2))

becomes

HAVING (MIN(col2) < MAX(col2)) OR FALSE

becomes

HAVING (MIN(col2) < MAX(col2))

19 May 2006 7:41 PM

Stu

The quickest method I could come up with is

SELECT col1
FROM (SELECT DISTINCT col1, col2
FROM test) t
GROUP BY col1
HAVING COUNT(*) > 1

Others might have better solutions.

Stu

19 May 2006 7:49 PM

Vern Rabe

Try this:

SELECT col1
    FROM (SELECT col1, col2
        FROM test
        GROUP BY col1, col2) x
    GROUP BY col1
    HAVING count(*) > 1;

HTH
Vern

Show quote

"Hassan" wrote:

> Create table test
> (col1 varchar(50),col2 int)
>
> insert test values ('ABC',1)
> insert test values ('ABC',2)
> insert test values ('XYZ',1)
> insert test values ('XYZ',1)
>
>
> I would like the output to report those unique values in Col1 that have
> different values in Col2.. So the output based upon the input in table test
> would return
> 'ABC'
>
>
>

19 May 2006 8:04 PM

Alexander Kuznetsov

select col1
from test
group by col1
having count(distinct col2)>1

19 May 2006 8:42 PM

Jim Underwood

Definitely simpler and more straight forward than what the rest of us had.

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1148069052.652587.265840@u72g2000cwu.googlegroups.com...
> select col1
> from test
> group by col1
> having count(distinct col2)>1
>