CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
prince2020
prince2020

Reputation: 61

How to remove duplicate rows in SQL

I want to remove duplicate rows in the following data.

Data:

a       a
a       b
a       c
a       d
a       e
b       a
b       b
b       c
b       d
b       e
c       a
c       b
c       c
c       d
c       e
d       a
d       b
d       c
d       d
d       e
e       a
e       b
e       c
e       d
e       e

The output should be column1 and column2 different. and it should not be the following data.

a       b
a       c
a       d
a       e
b       a
b       c
b       d
b       e
c       a
c       b
c       d
c       e
d       a
d       b
d       c
d       e
e       a
e       b
e       c
e       d

because a in column 1 and b in column2 is same as b in column1 and a in column2 or else simply i mean to say 1+2 is same as 2+1.

So, the output should be

a,b
a,c
a,d
a,e
b,c
b,d
b,e
c,d
c,e
d,e

Upvotes: 0

Views: 200

Answers (2)

user330315
user330315

Reputation: 

select distinct least(column_1, column_2), greatest(column_1, column_2)
from the_table
where column_1 <> column_2;

Upvotes: 3

Bulat
Bulat

Reputation: 6979

This should give you what you need with a bit of reshuffle:

SELECT DISTINCT LEAST(column1, column2) as column1, GREATEST(column1, column2) as column2
FROM myTable

If you want to keep values in columns they belonged originally, try this:

WITH cte AS 
( SELECT t.*, ROW_NUMBER() OVER () RN
  FROM   myTable t)
SELECT * FROM cte t
WHERE NOT EXISTS (
   SELECT * FROM cte 
   WHERE 
      t.column1 IN (column1, column2) AND 
      t.column2 IN (column1, column2) AND
      t.RN > RN
  )

Upvotes: 1

Related Questions