How to query SQL Table and remove duplicate rows from a result set

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.