How to remove duplicate rows in SQL

Question

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

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Sep 12 '14 at 06:39
@Nagaraju. Read your question again. "It should not be the following data" mentions a,b and b,a. "The output should be" mentions a,b. So what is it? And don't call it "duplicate rows" becauses that's not what they are. Please [edit](http://stackoverflow.com/posts/25802219/edit) — Jan Doggen, Sep 12 '14 at 06:51

score 3 · Answer 1 · 2014-09-12T10:29:45.557

3

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

edited Sep 12 '14 at 10:29

answered Sep 12 '14 at 06:40

wouldn't that leave "a, a" there? I think there should be a "where column_1!=column_2" – a.j. tawleed Sep 12 '14 at 09:47
@user2179887: yes, if you don't want `(a,a)` in the result you need exclude that through a `where` clause. – Sep 12 '14 at 10:30
@a_horse_with_no_name,what if I use SQL SERVER which doesn't have LEAST FUNCTION? – Héléna Jul 22 '16 at 05:22

Bulat · Answer 2 · 2014-09-12T07:40:56.813

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
  )

How to remove duplicate rows in SQL

2 Answers2

Linked