q; how to write this

Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=â'Yesâ' first
2. Then select all the ID distinct where IsYesNo=â'Noâ' if ID is not in the
first selection
Combine these two and return the resultTry:
select distinct
ID
, IsYesNo
from
MyTable
where
IsYesNo = 'Yes'
union all
select distinct
ID
, IsYesNo
from
MyTable o
where
IsYesNo = 'No'
and not exists
(
select distinct
*
from
MyTable i
where
i.IsYesNo = 'Yes'
and
i.ID = o.ID
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:7E592B5E-81BB-420A-BDBE-9ED0C67689F4@.microsoft.com...
Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=â'Yesâ' first
2. Then select all the ID distinct where IsYesNo=â'Noâ' if ID is not in the
first selection
Combine these two and return the result|||Looks like homework so ignoring the detail of the question but going straight
to the result
select ID, IsYesNo = max(IsYesNo)
from MyTable
Group by ID
"JIM.H." wrote:
> Hello,
> I have MyTable with ID, IsYesNo fields
> ID is duplicated so I need perform select on MyTable with the following
> conditions:
> 1. Select all the ID distinct where IsYesNo=â'Yesâ' first
> 2. Then select all the ID distinct where IsYesNo=â'Noâ' if ID is not in the
> first selection
> Combine these two and return the result
>