I know this is simple...
Please advise on how I can get a result set of rows 1, 9, 18, and 21 (based on the attached image)??
Thanks,
Brad
Add a comment
|
3 Answers
If the rows are truly distinct across every column, then you can use SELECT DISTINCT.
Since you are using SQL Server you can also use row_number() to return one row for each ThreatId:
select ThreatId,
ThreatTopClient,
...
from
(
select ThreatId,
ThreatTopClient,
...,
row_number() over(partition by ThreatId order by ThreatMLSeq) rn
from xThreatCA
where ThreatMLSeq <> N''
and ID <> 0
) d
where rn = 1
order by ThreatMLSeq
Comments
Use SELECT DISTINCT instead of SELECT
SELECT DISTINCT ThreatID, ThreatTopClient,
'#' + CONVERT(NVARCHAR(2), ThreatMLSeq) + ' -- ' + ThreatML AS CAMLPad,
ThreatMLSeq, ThreatML, ThratDetailClient, ThreatArea,
ThreatFinalInherentRisk, ThreatTier21, ThreatControls, AuditID
FROM xThreatCA
WHERE (ThreatMLSeq <> N'') AND (ID <>0)
ORDER BY dbo.xThreatCA.ThreatMLSeq
1 Comment
blub
Thank you for responding... I appreciate your help!
Since there exists a redundant data in your table you can use the following approach:-
Create a temp table (with same schema as of your attached image table) and execute this query:-
Insert into [temptable]
Select [all column names] from [table]
union
Select [all column names] from [table]
afterwards you can truncate data from your original table and then do insert from temptable to original table.
