In a select statement, I want to evaluate a value, and when I get that value, I want the value for all other rows to be set to that value so I end up with just one row.
For example:
Meeting# Vote
12345 Yes
12345 Maybe
12345 No
12345 See Comment
12345 Yes
Whenever I get a vote that says, See Comment, I want the end result to look like:
Meeting# Vote
12345 See Comment
If my result does not have See Comment, Yes will be next in line:
Meeting# Vote
12345 Yes
12345 Maybe
12345 No
12345 Yes
Whenever I get a vote that says, Yes, I want the end result to look like:
Meeting# Vote
12345 Yes
And so on.... I hope this makes sense.
Thanks, Iris
Try:
select
*
from
dbo.t1 as a
where
Vote = (
select top 1 Vote
from dbo.t1 as b
where b.Meeting# = a.Meeting#
order by
case
when Vote = 'See Comment' then 1
when Vote = 'Yes' then 2
when Vote = 'Maybe' then 3
when Vote = 'No' then 4
else 5
end
)
-- 2005
;with cte
(
select
Meeting#,
Vote,
row_number() over(partition by Meeting# order by
case
when Vote = 'See Comment' then 1
when Vote = 'Yes' then 2
when Vote = 'Maybe' then 3
when Vote = 'No' then 4
else 5
end
) as rn
from
dbo.t1
)
select
Meeting#, Vote
from
cte
where
rn = 1
go
AMB
|||The first row for all of my meeting#'s was 'No'. So when select top 1 Vote is used, it got 'No' everytime, and set everything to no.
For the first meeting#, it looked like:
Meeting# Vote
12345 No
12345 Yes
12345 See Comment
12345 Yes
It took 'No' first, and went on to the next Meeting#. What I want it to do is be selective. If 'See Comment' exists, it takes precedent over 'Yes' and 'No'. If 'See Comment' does not exist, it will then look for 'Yes'. Then if 'Yes' or 'See Comment' does not exist, it will then set to 'No'.
Thanks, Iris
|||It works for as expected for the sample data. If it is not what you expect, then post sample data and expected result.
use northwind
go
Code Snippet
createtable #t (
Meeting# intnotnull,
Vote varchar(25)notnull
)
insertinto #t values(12345,'No')
insertinto #t values(12345,'Yes')
insertinto #t values(12345,'See Comment')
insertinto #t values(12345,'Yes')
insertinto #t values(12346,'No')
insertinto #t values(12346,'Yes')
insertinto #t values(12346,'Yes')
insertinto #t values(12347,'No')
insertinto #t values(12347,'Maybe')
insertinto #t values(12348,'No')
selectdistinct
*
from
#t as a
where
Vote =(
selecttop 1 Vote
from #t as b
where b.Meeting# = a.Meeting#
orderby
case
when Vote ='See Comment'then 1
when Vote ='Yes'then 2
when Vote ='Maybe'then 3
when Vote ='No'then 4
else 5
end
)
orderby
Meeting#
;with cte
as
(
select
Meeting#,
Vote,
row_number()over(partitionby Meeting# orderby
case
when Vote ='See Comment'then 1
when Vote ='Yes'then 2
when Vote ='Maybe'then 3
when Vote ='No'then 4
else 5
end
)as rn
from
#t
)
select
Meeting#, Vote
from
cte
where
rn = 1
orderby
Meeting#
droptable #t
go
AMB
|||Thanks, that worked.
Iris
