2

Please login or register to vote for this query.

(click on this box to dismiss)

Find the top comments, or comments with specific text or by users with a specific name. Names and comment text can contain the wildcard "%".

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language

-- UserId: User ID "zero to disable"
declare @user_id int = ##UserId:int?0##
-- UserName: User Name "% to disable"
declare @user_name nvarchar(40) = ##UserName:string?%##
-- text: Text (% to match all)
declare @text nvarchar(600) = ##text:string?%##
-- SearchQuestions: Search questions "0=no, 1=yes"
declare @searchQ int = ##SearchQuestions:int?1##
-- SearchAnswers: Search answers "0=no, 1=yes"
declare @searchA int = ##SearchAnswers:int?1##

select top (##TopN:int?100##)
    row_number() over (order by c.Score desc, c.CreationDate desc) as Rank,
    (case when c.UserId is not null then
      'site://users/' + cast(c.UserId as nvarchar) + '|' else '' end) +
      coalesce(Users.DisplayName, c.UserDisplayName) as [User Link],
    c.Score,
    c.Id as [Comment Link],
    c.CreationDate
into #Results
from Comments c
left join Users on Users.Id = c.UserId
left join Posts on Posts.Id = c.PostId
where lower(Text) like N'%' + lower(@text) + '%'
    and (@user_id = 0 or c.UserId = @user_id)
    and lower(coalesce(Users.DisplayName, c.UserDisplayName)) like lower(@user_name)
    and ((@searchQ = 1 and Posts.PostTypeId = 1)
      or (@searchA = 1 and Posts.PostTypeId = 2))

select row_number() over (order by Rank) as Rank,
    [User Link], Score, [Comment Link], CreationDate
from #Results

select Rank, Score
from #Results
order by Rank

fork query

edited oct 17 16 amaranth

created jul 22 10 krock