8

I'm doing my part to burn down the Close Vote queue.

Is there a way to find all of the (non-deleted) questions where my Vote to Close was the final vote?

  • Are you specifically looking for this information in the data explorer, or in general? – Aza Mar 11 '14 at 1:31
  • @Emracool: Either or. I was figuring that I was most likely going to be using the data explorer, but if there's another way I'd be happy to use it. – ale Mar 11 '14 at 1:32
6

That data is not directly available in SEDE but by sheer luck the query Where close voted I as the last one gives you that result.

-- UserId: Your User id "That is the id in url on the site"
declare @userid as nvarchar(32)
declare @uid as int = ##UserId##
select @userid='"Id":'+cast(@uid as nvarchar)+','

-- if we reverse the text field the last voter is first 
-- we determine the Id field position
-- and the Id+userid position. If they differ by the lenght
-- of the userid the user was the last one voting
select postid as [Post Link]
, @userid, len(@userid)
, [text]
, reverse([text])
, charindex(':"dI"{', reverse([text]))
, charindex(reverse(@userid), reverse([text]))
from posthistory
where posthistorytypeid = 10 -- close
and [text] like '%'+@userid+'%'
and (charindex(':"dI"{', reverse([text])) - (len(@userid)-5) = 
    charindex(reverse(@userid), reverse([text])))

The success (or failure) of this query depends on the EXACT json structure in the [text] field and the assumption the users are added in order they voted. If for some reason additional data is added that also has an 'id' as one of his members then this query would need to do a full parse of the text field...

Example of the content in that field:

{"Voters":[{"Id":207421,"DisplayName":"EJP"},{"Id":647772,"DisplayName":"Tichodroma"},{"Id":131433,"DisplayName":"bmargulies"},{"Id":717341,"DisplayName":"Lukas Knuth"},{"Id":21960,"DisplayName":"Al Everett"}]}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .