This was asked in the SQL Helpline room:
I have a sql query concern. Could you help me? It goes like this. I have a table with the Records A B A C B A B C C A C B How do I get only the records that there aren't duplicated in both columns A B A C B C
While it seemed simple at first, it was surprisingly difficult to get it to work. In the end, I had to resort to a loop. Please mind, this looks like example code, but it is more like practice code, so that the logic can be applied to real situations.
I saved a demo here (which uses temp tables) and the query goes like this:
/* Assuming that @Origin is the source table your data currently is: */ declare @Origin table (val1 char(1), val2 char(1)); insert into @Origin (val1, val2) values ('A', 'B'), ('A', 'C'), ('B', 'A'), ('B', 'C'), ('C', 'A'), ('C', 'B'); /* Create a temp table in memory: Note: We could also use a physicial #holdingTable if the result set is too large for memory */ declare @holdingTable table ( rownum int primary key, val1 char(1), val2 char(1) ); insert into @holdingTable ( rownum, val1, val2 ) select /* generate a rownum to use for looping: */ row_number() over (order by val1, val2), /* add values from origin table: */ val1, val2 from @Origin; /* loop variables: */ declare @i int = (select min(rownum) from @holdingTable); declare @max int = (select max(rownum) from @holdingTable); /* deduplication variables: */ declare @val1Deduplicator char(1); declare @val2Deduplicator char(1); /* table to hold result set until end of query: */ declare @resultSet table (val1 char(1), val2 char(1)); while @i <= @max begin /* assign values to deduplicate across columns: */ select /* inversion between val1 and val2 happens here: */ @val1Deduplicator = val2, @val2Deduplicator = val1 from @holdingTable where rownum = @i; /* verify we are not inserting a duplicate: */ if not exists ( select 1 from @resultSet where val1 = @val1Deduplicator and val2 = @val2Deduplicator ) /* if confirmed not a duplicate, insert the values from the current row: */ begin insert into @resultSet (val1, val2) select val1, val2 from @holdingTable where rownum = @i; end; /* increment loop: */ set @i = @i + 1; end; /* obtain the final results: */ select * from @resultSet;
The performance seems OK for this few records, but it's not set-based and might get really slow for a large number of records. Is there a smarter way to do this? Is there anything else I can improve?
Output is correct:
val1 val2 ---- ---- A B A C B C (3 row(s) returned)