CODEWITHSUNDEEP
sql
Siddhant Aggarwal
Siddhant Aggarwal

Reputation: 19

Comparing a particular column value for two rows in SQL

I have a table as follows:

ID  Version Type  Value
121    1     A     100
121    1     B     200
122    2     A     300
122    2     B     300
123    3     A     300
123    3     B     300
124    4     A     420
124    4     B     420
121    3     A     410
121    3     B     410

I am trying to build a query which gives returns the rows where "Value" is different between Type A and Type B for same ID and Version. For example, in the above scenario, ID and Version is Unique 5 times (121-1,122-2,123-3,124-4,121-3). I would look at "Type" for each unique group and compare "Value" for Type A and Type B. The above scenario should return:

ID  Version Type  Value
121    1     A     100
121    1     B     200

The above two rows are the only ones with different "Value" for unique ID and Version and among Type A and Type B. Any help will be greatly appreciated :)

Upvotes: 1

Views: 71

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If you have only two types, I am going to recommend a different result set format:

select id, version,
       max(case when type = 'A' then value end) as value_a,
       max(case when type = 'B' then value end) as value_b
from t
where type in ('A', 'B')
group by id, version,
having max(case when type = 'A' then value end) <> max(case when type = 'B' then value end);

I recommend this because a typical follow-on question might be "how big is the difference".

Upvotes: 1

Siddhant Aggarwal
Siddhant Aggarwal

Reputation: 19

This is what I ended up doing:

SELECT MAX(Value), MIN(Value)
FROM table_name
GROUP BY ID, Version
HAVING MAX(Value)<>MIN(Value);

Upvotes: -1

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

You can join a table to itself:

SELECT *
FROM [MyTable] t0
INNER JOIN [MyTable] t1 ON t1.ID = t0.ID AND t0.Type <> t1.Type AND t0.Value <> t1.Value

or

SELECT *
FROM [MyTable] t0
INNER JOIN [MyTable] t1 ON t1.ID = t0.ID AND t1.Type = 'B' AND t0.Value <> t1.Value
WHERE t0.Type = 'A'

Upvotes: 1

VBoka
VBoka

Reputation: 9083

This is one way to approach this problem:

select * 
from t1 
where (id, value, version) in (select id, value, version
                      from t1 
                      group by id, value, version
                      having count(id) = 1);

Here is a DEMO

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use exixts :

select t.*
from table t
where exists (select 1 
              from table t1 
              where t1.id = t.id and 
                    t1.Version = t.Version and 
                    t1.type <> t.type and 
                    t1.Value <> t.Value
             );

Upvotes: 3

Related Questions