Identify Records with Same Values in Multiple Columns 1

[tklear]

I have a table with 5 columns that each contain a value from 1-13. I want to find those records where the same value occurs in more than one of the 5 columns. Is there a function that will handle this? So far I have this idea but am looking for an easier/faster way:

select fooID
from foo
where p1=p2 or p1=p3 or p1=p4 or p1=p5
or p2=p3 or p2=p4 or p2=p5 or p3=p4 or p3=p5 or p4=p5.

I actually have 10 columns so it will be more combinations. Any ideas for a better way.

Thanks!

 

[swampBoogie]

I don't see any alternative. You can save some typing by

where p1 in (p2,p3,p4,p5,p6,p7,p8,p9,p10)
   or p2 in (p3,p4,p5,p6,p7,p8,p9,p10)
-- etc

 

[karluk]

You might be able to take advantage of the fact that your data contains only the small number of known values 1 through 13. Consider an expression such as

stuff(stuff(stuff(stuff(stuff(stuff(stuff(stuff(stuff(stuff
('0000000000000',p1,1,'1')
,p2,1,'1')
,p3,1,'1')
,p4,1,'1')
,p5,1,'1')
,p6,1,'1')
,p7,1,'1')
,p8,1,'1')
,p9,1,'1')
,p10,1,'1')

The values taken on by p1 through p10 determine which '0' characters are replaced with '1'. For example, if p1=6, then the sixth '0' becomes a '1'.

The key point is that the number of '1's in the expression will be decreased by one for every duplicate value in columns p1 through p10. There will only be ten '1's if there are no duplicates. Otherwise there will be fewer. As a result, you can select rows with duplicate values by counting the number of '1's:

select fooID from foo
where
len(replace(
stuff(stuff(stuff(stuff(stuff(stuff(stuff(stuff(stuff(stuff
('0000000000000',p1,1,'1')
,p2,1,'1')
,p3,1,'1')
,p4,1,'1')
,p5,1,'1')
,p6,1,'1')
,p7,1,'1')
,p8,1,'1')
,p9,1,'1')
,p10,1,'1')
,'0','')) < 10