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)??

Thanks,

Brad

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

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

Need Your Help

what is the difference between hive jdbc client and hive metastore java api

java api hadoop jdbc hive

Hey guys I was using hive jdbc but after that i came to know that there is hive metastore java api (here) by which you can again connect to hive and manipulate hive database

Install ruby gem feedjira on codingground online coding platform

ruby rubygems

I cannot install feddjira on Codingground online coding plataform (http://www.tutorialspoint.com/execute_ruby_online.php) via

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.