5

Please login or register to vote for this query.

(click on this box to dismiss)

Get the posts that currently have solved, fixed, answered, or updated in the title, removing those that haven't been edited, filtering duplicates.

Q&A about the site for serious players and enthusiasts of chess

-- Reworked by Deduplicator
-- Use of `collate` suggested by Rene

with candidates as (
  select postid, max(id) historyid
  from posthistory
  where posthistorytypeid = 4
  group by postid
)
select p.tags, p.id [Post Link], p.lastactivitydate [Last Active], h.Comment, p.AcceptedAnswerId [Accepted], p.ClosedDate [Closed]
from candidates
join posts p on p.id = postid
join posthistory h on h.id = historyid
where upper(Title) like '%[\[(<{]SOLVED[\])>}]%' escape '\'
or Title like 'SOLVED%-%' collate SQL_Latin1_General_CP1_CI_AI
or Title like '%[\[(<{]FIXED[\])>}]%' escape '\' collate SQL_Latin1_General_CP1_CI_AI
or Title like 'FIXED %-%' collate SQL_Latin1_General_CP1_CI_AI
or Title like '%[\[(<{]ANSWERED[\])>}]%' escape '\' collate SQL_Latin1_General_CP1_CI_AI
or Title like 'ANSWERED%-%' collate SQL_Latin1_General_CP1_CI_AI
or Title like '%[\[(<{]RESOLVED[\])>}]%' escape '\' collate SQL_Latin1_General_CP1_CI_AI
or Title like 'RESOLVED%-%' collate SQL_Latin1_General_CP1_CI_AI
order by lastactivitydate desc

fork query

edited apr 30 18 Cœur

created nov 1 15 theB