CODEWITHSUNDEEP
sql
Praveen
Praveen

Reputation: 94

Distinct by comparing multiple columns SQL

I have a select query 

Select col1,col2,col3 
from table;

The table contains following rows

col1 col2 col3 
A    | B  | C
B    | A  | C
C    | B  | C

I need to get the distinct result which contains a single combination A,B,C by comparing multiple columns.

Result Should be some thing like 

col1 col2 col3 
A    | B  | C

the order can be changed of result rows.

How can I achieve this ?

Upvotes: 5

Views: 79

Answers (3)

Sridhar DD
Sridhar DD

Reputation: 1980

You can also go with this below query if the number of column and the values are known.

The CASE statement is the closest to IF in SQL

SELECT
         CASE 
              WHEN (col1 = 'A' and col2 = 'B' and col3='C') or (col1 = 'C' and col2 = 'A' and col3='B') or (col1 = 'B' and col2 = 'C' and col3='A' )
                 THEN 1 
              ELSE 0 
         END  as RESULT, * 
FROM table

From the result you can take the required output by checking the value of RESULT==1(integer)

If you want the result as a boolean value then do the CAST like , 

SELECT
     CAST(
         CASE 
              WHEN (col1 = 'A' and col2 = 'B' and col3='C') or (col1 = 'C' and col2 = 'A' and col3='B') or (col1 = 'B' and col2 = 'C' and col3='A' )
                 THEN 1 
              ELSE 0 
         END  as RESULT_INT)
     as RESUTL, * 
FROM table

Upvotes: 0

AK47
AK47

Reputation: 3807

Please try out this, I am not sure about you proper requirement. But on sample data given above. I have came across this solution,

With CTE as
(
     Select MIN(col1) as col1 from MyTable
)
Select * from CTE
cross apply
    (
        Select MIN(col2) as col2 from MyTable
        where col2 <> CTE.col1
    )as a
cross apply
    (
        Select MIN(col3) as col3 from MyTable
        where col3 not in (CTE.col1,a.col2)
    )as b

DEMO HERE

Upvotes: 2

Matt
Matt

Reputation: 15061

SELECT *
FROM table
WHERE (col1 = 'A' AND col2 = 'B' AND col3 = 'C')

Upvotes: 0

Related Questions