Column X is same, column Y is different?

6 Jan 2006 4:09 PM

Rick Charnes

Can someone point me in the right direction: How do I say: "Display all
rows from MYTABLE where column x is the same but column y is different"?
Thanks much.

6 Jan 2006 4:19 PM

SQL

If I understand your question correctly then this will do it
select * from table where colX = someValue
and colY <> someValue

example

select * from employees where FirstName= 'John'
and Zipcode<> '10028'

http://sqlservercode.blogspot.com/

6 Jan 2006 4:29 PM

Rick Charnes

Sorry; my fault: I don't know the values that I'm comparing. I want to
say: give me all rows in the table where col X is the same but within
those matched rows col Y is different. But I don't have any specific
values of X or Y that I'm comparing.

I think I need to use:
GROUP BY x
HAVING count(*) > 1
....and something with column Y <> column Y...?

somehow, but I'm not sure how.

In article <1136564368.957582.7***@o13g2000cwo.googlegroups.com>,
denis.g***@gmail.com says...
Show quote

> If I understand your question correctly then this will do it
> select * from table where colX = someValue
> and colY <> someValue
>
> example
>
> select * from employees where FirstName= 'John'
> and Zipcode<> '10028'
>
> http://sqlservercode.blogspot.com/
>
>

6 Jan 2006 4:43 PM

David Portas

Rick Charnes wrote:
Show quote

> Sorry; my fault: I don't know the values that I'm comparing. I want to
> say: give me all rows in the table where col X is the same but within
> those matched rows col Y is different. But I don't have any specific
> values of X or Y that I'm comparing.
>
> I think I need to use:
> GROUP BY x
> HAVING count(*) > 1
> ...and something with column Y <> column Y...?
>
> somehow, but I'm not sure how.
>
> In article <1136564368.957582.7***@o13g2000cwo.googlegroups.com>,
> denis.g***@gmail.com says...
> > If I understand your question correctly then this will do it
> > select * from table where colX = someValue
> > and colY <> someValue
> >
> > example
> >
> > select * from employees where FirstName= 'John'
> > and Zipcode<> '10028'
> >
> > http://sqlservercode.blogspot.com/
> >
> >

Try:

SELECT T1.*   -- List the column(s) don't use *
FROM your_table AS T1
WHERE EXISTS
(SELECT *
   FROM your_table AS T2
   WHERE T1.x = T2.x
    AND T1.y <> T2.y);

Or maybe:

SELECT x
FROM your_table
GROUP BY x
HAVING MIN(y)<MAX(y);

depending on what column(s) you want to output.

--
David Portas
SQL Server MVP
--

6 Jan 2006 4:44 PM

SQL

Here is one way

create table mark (value varchar(50),id int)
insert into mark
select 'AA',1 union all
select 'AA',1 union all
select 'AB',2 union all
select 'AB',1

select distinct m.* from mark m join(
select value from mark
group by id,value
having count(*) =1) m2 on m.value =m2.value

http://sqlservercode.blogspot.com/

6 Jan 2006 4:49 PM

ML

Post DDL, sample data and expected results, then.

ML

---
http://milambda.blogspot.com/

6 Jan 2006 4:52 PM

Alexander Kuznetsov

Not sure what you really need, that's my understanding:

create table #t(x int not null, y int)

----- in this group all the y's are different
insert into #t values(1,1)
insert into #t values(1,2)
insert into #t values(1,3)

----- in this group some y's are the same
insert into #t values(2,1)
insert into #t values(2,2)
insert into #t values(2,2)

select x, count(*), count(distinct y) from #t group by x

select * from #t where x in(
select x from #t group by x
having count(*) = count(distinct y)
)

x           y
----------- -----------
1           1
1           2
1           3

(3 row(s) affected)

6 Jan 2006 4:23 PM

Lee

SELECT a.*
FROM MYTABLE as a, MYTABLE as b
WHERE a.x = b.x AND a.y <> b.y

Show quote

"Rick Charnes" wrote:

> Can someone point me in the right direction: How do I say: "Display all
> rows from MYTABLE where column x is the same but column y is different"?
> Thanks much.
>

6 Jan 2006 4:55 PM

Rick Charnes

This does it. Thanks very much.

In article <C8F7E55C-707D-43FA-B6ED-BC2697B8D***@microsoft.com>,
L**@discussions.microsoft.com says...
Show quote

> SELECT a.*
> FROM MYTABLE as a, MYTABLE as b
> WHERE a.x = b.x AND a.y <> b.y
>
> "Rick Charnes" wrote:
>
> > Can someone point me in the right direction: How do I say: "Display all
> > rows from MYTABLE where column x is the same but column y is different"?
> > Thanks much.
> >
>

6 Jan 2006 4:25 PM

ML

select distinct
         x
         ,y
         from table

?

DDL, sample data and expected results would help a lot.

ML

---
http://milambda.blogspot.com/

6 Jan 2006 4:25 PM

Ryan Powers

I'm not sure what you mean.

Column X is the same as what?
Column Y is different than what?

Are you comparing rows against specific values?
Or are you comparing the rows against the rows from another table.

Can you give us a simple example?

--
Ryan Powers
Clarity Consulting
http://www.claritycon.com

Show quote

"Rick Charnes" wrote:

> Can someone point me in the right direction: How do I say: "Display all
> rows from MYTABLE where column x is the same but column y is different"?
> Thanks much.
>