How to query SQL Table and remove duplicate rows from a result set
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)??
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
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