CODEWITHSUNDEEP
sqlsqlite
Orphean Sodality
Orphean Sodality

Reputation: 33

SQL - matching similar records with same set of values in jumbled order

Say I have a table with the following values in three columns

John Smith, Edward Jones, 4  
John Deer, Jane Deer, 2  
Edward Jones, John Smith, 4

I would like to have a query that recognizes the first and third records as similar, selecting only the first and second records and leaving the third out. I can easily do this when the values are in the same order, but I'm having trouble coming up with something that can discern similarity when the values are in arbitrary order like this. Any ideas?

[edited for clarity - this is a table with three columns]

Upvotes: 0

Views: 97

Answers (1)

Ian Kenney
Ian Kenney

Reputation: 6426

You should be able to use case statements to put the name columns into a consistent order. Something like

create table n( n1 varchar(20), n2 varchar(20), v int);

insert into n select "john", "edward",4;
insert into n select "john", "jane", 3;
insert into n select "edward","john",4;



select distinct
  case when n1>n2 then n1 else n2 end n1,
  case when n1>n2 then n2 else n1 end n2,
  v
from n

Upvotes: 1

Related Questions