CODEWITHSUNDEEP
sql
user2060375
user2060375

Reputation: 1

SQL: How to create result as shown below

I facing issue with the below case:

I/P Table:

Column_1  Column_2
A             A 
B             B 
C             C
D             D

Cross Join will give me the below result

Actual O/P Value:
AA
AB--This combinati

on is Repeated
    AC
    AD

BA--This combination is Repeated
BB
BC
BD

CA
CB
CC
CD

DA
DB
DC
DD

Expected OP:

AA
AB
AC
AD

BB
BC
BD

CC
CD

DD

I dont want the combinations to be repeated: Like AB and BA.

Upvotes: 0

Views: 183

Answers (2)

user330315
user330315

Reputation: 

select distinct least(column_1, column_2), greatest(column_1, column_2)
from ip_table 
   cross join ip_table
order by 1

Upvotes: 0

Anton Kovalenko
Anton Kovalenko

Reputation: 21507

Add an extra condition requiring the second value to be greater or equal to the first:

.... WHERE (Column_2 >= Column_1)

Upvotes: 2

Related Questions