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)