2

Is it possible to know (be sure) the sequence of review tasks results, even though the time data from 'CreationDate' is removed (i.e., there is only date, not time)?

Let me explain better. I want to know the sequence of actions a post receive in the Close Vote review queue. For this I wrote the following query:

SELECT TOP(100) p.Id AS [Post Link],
                rtrt.Name AS [Review Task Result Name],
                rtr.CreationDate [Date from Review Task Result],
                ROW_NUMBER() OVER(PARTITION BY p.Id ORDER BY rtr.CreationDate ASC) AS [Sequence] 
FROM PostsWithDeleted p
INNER JOIN ReviewTasks rt ON rt.PostId = p.Id
INNER JOIN ReviewTaskResults rtr ON rtr.ReviewTaskId = rt.Id
INNER JOIN ReviewTaskResultTypes rtrt ON rtrt.Id = rtr.ReviewTaskResultTypeId
WHERE rt.ReviewTaskTypeId = 2 -- Close Votes review queue
      AND rt.ReviewTaskStateId = 2 -- Review was completed
      AND rtr.ReviewTaskResultTypeId IN (5, 6, 8) -- Edit, Close, Do not close; respectively
      AND p.CreationDate < GETDATE() - 15
ORDER BY p.Id DESC,
         rtr.CreationDate ASC;

I tracked the order of Review Tasks Results from two posts returned by the query, and the sequence in them seemed to be correct:

Was it a coincidence? If it is not a coincidence how is it possible the sequence is correct if rtr.CreationDate has no time data, only date; and there are multiple actions within a unique date?

I think I am lacking some understanding in how SQL sorting works generally and/or with datetime datatype.

Improve this question
2

ReviewTaskResults has a primary key Id, which is highly likely to have an auto increment. So you can just sort on Id instead of on CreationDate.

Improve this answer
1
  • There are a few cases where race conditions might alter the order if you would look at timestamps but I highly doubt you would find many within this specific table. Also note that the Id might not be consecutive and that is not only due to deletes.
    – rene
    Feb 17 '19 at 21:42

You must log in to answer this question.

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