May 17, 2013 at 10:06 am
I have a table where the unique identifier is studentId.
Now this table contains decision column
If decision has been made, then decision=’yes’/’No’
If decision hasn’t been made decision=’none’
Now the tables gets appended when the decision has been made, so for a student, we get 2 rows one, with decision=’none’ and other with decision=’yes’/’No’
I just want to delete the row for decision =’none’ when there is a yes/no decision.
For each row there is a unique DecId.
So for 2 rows with same emplid, we have 2 different DecId.
How do I do that?
Thanks,
Blyzzard
May 17, 2013 at 10:12 am
if DecId is an identity() column, can can infer that if the data was ordered by DecId, only the last record applies;
Since this looks a lot like homework, I'll give you a general answer
i'd use the row_number function and make sure i use the partition by statement, and order by the DecId;
Lowell
May 17, 2013 at 10:13 am
amar_kaur16 (5/17/2013)
I have a table where the unique identifier is studentId.Now this table contains decision column
If decision has been made, then decision=’yes’/’No’
If decision hasn’t been made decision=’none’
Now the tables gets appended when the decision has been made, so for a student, we get 2 rows one, with decision=’none’ and other with decision=’yes’/’No’
I just want to delete the row for decision =’none’ when there is a yes/no decision.
For each row there is a unique DecId.
So for 2 rows with same emplid, we have 2 different DecId.
How do I do that?
Thanks,
Blyzzard
Like this (untested as you didn't give us anything to work with):
with BaseData as (
select
StudentId,
Decision, -- ('Yes','No', or 'None' for this example)
rn = row_number() over (partition by StudentId order by case when Decision = 'None' then 1 else 0 end asc)
from
dbo.MyTable -- Don't know your table name
)
delete from BaseData where rn > 1;
May 17, 2013 at 12:27 pm
Thank You Lynn.
It worked.
May 22, 2013 at 4:00 am
you can use row number with partitions to do the same
May 22, 2013 at 4:13 am
subhajeetsur (5/22/2013)
you can use row number with partitions to do the same
I think that is exactly what Lynn has suggested above.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy