I have a table like this:
Id GroupId StdId Active
---------------------------
1 1 1 true
2 1 2 false
3 1 1 false
4 1 1 false
5 1 1 true
6 1 2 true
I want to delete duplicate row but if Active have true/false value keep true value and delete false value.
For example I want to this list
Id GroupId StdId Active
---------------------------
1 1 1 true
6 1 2 true
You can use the following query:
;WITH ToDelete AS (
SELECT Id, GroupId, StdId, Active,
ROW_NUMBER() OVER (PARTITION BY GroupId, StdId
ORDER BY Active Desc, Id) AS rn
FROM mytable
)
DELETE FROM ToDelete
WHERE (rn > 1) OR (rn =1 AND Active = 0)
The above assumes that Active is actually a bit field.
ROW_NUMBER helps us identify duplicate records. Those with Active=1 will take precedence over those with Active=0 within each GroupId, StdId partition. Using a CTE we can easily delete all duplicate rows filtering out the top level record, in case it is an Active=1 one.
(rn =1 AND Active = 0) predicate. The OP can leave this out if he wants to always leave one record. But I think this is not what he actually wants.I suspect that you want to keep one row per StdId, with a preference for true rows. If so:
with todelete as (
select t.*,
row_number() over (partition by groupid order by active desc, id desc) as seqnum
from table t
)
delete from todelete
where seqnum = 1;
This keeps exactly one record per stdId, with preference for keeping an active one. If all the records are false, then it keeps the false one with the largest id.
