CODEWITHSUNDEEP
sqlsql-server-2008
gmaximus
gmaximus

Reputation: 309

selecting rows that are the same in one column, but different in another

X    Y    DATE
1    20   20120101
1    21   20120101
2    30   20120201
3    40   20120201
3    41   20120301

I want to select any rows that have another row where X is the same, but the date is different, i.e. the answer would be

3    40   20120201
3    41   20120301

Upvotes: 18

Views: 19427

Answers (3)

Andrew Logvinov
Andrew Logvinov

Reputation: 21831

select distinct t1.*
  from table t1
  join table t2
    on (t1.X = t2.X and t1.date <> t2.date);

Upvotes: 3

t-clausen.dk
t-clausen.dk

Reputation: 44316

declare @t table(X int, Y int, DATE CHAR(8))
insert @t values
(1, 20, '20120101' ),
(1, 21, '20120101' ),
(2, 30, '20120201'),
(3, 40, '20120201'),
(3, 41, '20120301')

select x,y, date, maxy, miny from
(
  select *, max(date) over (partition by x) maxdate, 
  min(date) over (partition by x) mindate
  from @t
) a
where mindate <> maxdate

Upvotes: 0

Yuck
Yuck

Reputation: 50825

Try this...

SELECT *
FROM YourTable
WHERE X IN (
  SELECT T1.X
  FROM YourTable T1 INNER JOIN
       YourTable T2 ON T1.X = T2.X
  WHERE T1.DATE <> T2.DATE
);

This should work in most ANSI-compliant database products.

Upvotes: 24

Related Questions