Select distinct(columnName) from a table having CO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select distinct(columnName) from a table having CO

Author

Topic

misterraj
Yak Posting Veteran

94 Posts

Posted - 2015-03-18 : 01:14:28

I have a table like this with many records. for example:


HREFTopic                       PopularTopic                  LastModifiedTime
   ------------------------------------------------------------------------------
 //twitter.com/search?q=%23Happy    #Happy St. Patrick's Day    2015-03-17 19:57:18.000
 //twitter.com/search?q=%23Happy    #Happy St. Patrick's Day    2015-03-17 18:57:17.000
 //twitter.com/search?q=%23HappyV   #Happy Valentines Day   2015-03-17 17:57:15.000

This is my stored procedure


declare @abc nvarchar(max)  
 declare @SearchKey nvarchar(max) 
 select @SearchKey ='Happy'
 select top 10   @abc =  COALESCE(@abc + '','') + '<li>' + '<a class="z" href="' +'../q.aspx?q=' + @SearchKey + '">' + (PopularTopic)  + '</a></li>' + char(10) + char(13)      
 from PopularTrends  where HREFTopic like    '%'+ @SearchKey +'%'   order by LastModifiedTime desc   

 select @abc

and the output is (@abc) =


<li><a class="z" href="../q.aspx?q=Happy">Happy St. Patrick's Day</a></li>
 <li><a class="z" href="../q.aspx?q=HappyV">Happy Valentines Day  </a></li> 
 <li><a class="z" href="../q.aspx?q=Happy">Happy St. Patrick's Day</a></li> 
 <li><a class="z" href="../q.aspx?q=Happy">Happy St. Patrick's Day</a></li> 
 <li><a class="z" href="../q.aspx?q=Happy">Happy St. Patrick's Day</a></li>
 <li><a class="z" href="../q.aspx?q=Happy">Happy St. Patrick's Day</a></li>
 <li><a class="z" href="../q.aspx?q=Happy">Happy St. Patrick's Day</a></li>

but I need only distinct(PopularTopic) order by LastModifiedTime and I expect the result to be :


<li><a class="z" href="../q.aspx?q=Happy">Happy St. Patrick's Day</a></li>
 <li><a class="z" href="../q.aspx?q=HappyV">Happy Valentines Day  </a></li>

I am not able to get the distinct(PopularTopic), rather I get all the records. Can some one help me with getting the right query

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-18 : 09:45:47

First of all, know that assignments like this:


select @abc =  something + @abc

are not supported by Microsoft and can give erroneous results

Better to use FOR XML PATH('') instead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources