CODEWITHSUNDEEP
sqloracle-database
rakesh mn
rakesh mn

Reputation: 41

Compare 2 Column values in same table

I have a table with the following content:

a|b|c
-+-+-
1|2|3
2|2|2
3|2|4
4|4|4
5|6|7

I want to check if all values in are same and print a message if so. Example: Row 4,4,4 and 2,2,2 are having same column values. So I want to print "SAME" else print "Different"

How it can be done?

Upvotes: 1

Views: 95

Answers (3)

kkica
kkica

Reputation: 4104

Use this. Note that a=b and a=c means also b=c so you need only two comparisons.

select a,b,c, case when (a=b and a=c) then 'SAME' else 'Different' end as print
from tableName

EDIT: The case when a, b, c are all null, is a different case. If you want the rows where all 3 values are null to be considered the same, you can add or (a is null and b is null and c is null) or or coalesce(a,b,c)is null. Like this: 

select a,b,c, 
case when ((a=b and a=c) or(a is null and b is null and c is null)) then 'SAME' else 'Different' end as print
from tableName

OR

select a,b,c, 
case when ((a=b and a=c) or coalesce(a,b,c)is null) then 'SAME' else 'Different' end as print
from tableName

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If you have multiple, non-NULL columns, then a pretty general way is:

select t.*,
       (case when least(a, b, c) = greatest(a, b, c) then 'SAME' else 'DIFFERENT' end) as flag
from t;

This easily generalizes to more columns.

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use case when

    select case when (a=b and a=c ) then 'same' else 'Different' end as output
from t

Upvotes: 1

Related Questions