Recordset solution is sought. Please help.

-- First of all, thank you for your effort and your time.
--
-- I am looking for a record set solution to the following problem.
--
-- We have feature 1 2 and 3 and users define certain combinations of them.
These can be 2-way combinations where feature3 is null or 3 way combinations
-- For simplicity, let us assume we have values A B and C for these three
features.
-- Users can fill them in redundant ways as
-- ACB, CBA, BCA, CAB, ABC, BAC.
-- I would like to select a single occurrence, ABC, or one out of six,
always one distinct combination.
--
-- I have made an attempt however I remove all duplicate occurances. I just
don't see how I can resolve this. Please help.
--
-- Thanks again for your help.
--
set nocount on
declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
null, Feature2 varchar(10) null)
insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
insert @.tmp values ('C','B','A')
insert @.tmp values ('B','C','A')
insert @.tmp values ('C','A','B')
insert @.tmp values ('A','B','C')
insert @.tmp values ('B','A','C')
insert @.tmp values ('D','A','C') -- return either DAC or ACD
insert @.tmp values ('P','A','C')
insert @.tmp values ('N','B','D')
insert @.tmp values ('A','C','D')
SELECT *
FROM @.tmp t
WHERE NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
)
AND NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE -- 3 way
t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
t.feature1
OR
t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
t.feature
OR
t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
t.feature
)
-- Expected outcome:
A,B,C
D,A,C
P,A,C
N,B,DYou can add an Identity column to the temp table and check for Identity
column not equal in your sub query.
Perayu
"Farmer" wrote:

> -- First of all, thank you for your effort and your time.
> --
> -- I am looking for a record set solution to the following problem.
> --
> -- We have feature 1 2 and 3 and users define certain combinations of them
.
> These can be 2-way combinations where feature3 is null or 3 way combinatio
ns
> -- For simplicity, let us assume we have values A B and C for these three
> features.
> -- Users can fill them in redundant ways as
> -- ACB, CBA, BCA, CAB, ABC, BAC.
> -- I would like to select a single occurrence, ABC, or one out of six,
> always one distinct combination.
> --
> -- I have made an attempt however I remove all duplicate occurances. I jus
t
> don't see how I can resolve this. Please help.
> --
> -- Thanks again for your help.
> --
> set nocount on
> declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
> null, Feature2 varchar(10) null)
> insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
> insert @.tmp values ('C','B','A')
> insert @.tmp values ('B','C','A')
> insert @.tmp values ('C','A','B')
> insert @.tmp values ('A','B','C')
> insert @.tmp values ('B','A','C')
> insert @.tmp values ('D','A','C') -- return either DAC or ACD
> insert @.tmp values ('P','A','C')
> insert @.tmp values ('N','B','D')
> insert @.tmp values ('A','C','D')
>
> SELECT *
> FROM @.tmp t
> WHERE NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
> AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
> )
> AND NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE -- 3 way
> t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
> t.feature1
> OR
> t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
> t.feature
> OR
> t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
> t.feature
> )
> -- Expected outcome:
> A,B,C
> D,A,C
> P,A,C
> N,B,D
>
>|||Thank you for replying.
How do you suggest I do it? Please post what you think I can use.
"Perayu" <Perayu@.discussions.microsoft.com> wrote in message
news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
> You can add an Identity column to the temp table and check for Identity
> column not equal in your sub query.
> Perayu
> "Farmer" wrote:
>|||Farmer
Why not perfom such reports on the client side?
"Farmer" <someone@.somewhere.com> wrote in message
news:ukeQ0UzpFHA.3180@.TK2MSFTNGP15.phx.gbl...
> Thank you for replying.
> How do you suggest I do it? Please post what you think I can use.
>
> "Perayu" <Perayu@.discussions.microsoft.com> wrote in message
> news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
>