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:
- Example 1: https://stackoverflow.com/review/close/21970197.
- Example 2: https://stackoverflow.com/review/close/22101899.
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.