It's ordered by the "AwardedDate" of the bounty, newest first. This property isn't exposed in SEDE and is actually a layered relationship between the two bounty "votes." You won't be able to reproduce the sort in SEDE as a result. You'll just have to trust me, I guess. :-)
The active tab is quite similar, there's just more criteria added to the
WHERE clause, which makes the sorting completely up to SQL Server (as there is no creation date for the award since, by definition, it hasn't been awarded). Basically:
Now, these queries are simplified slightly from how they exist in the application code; basically, the order is determined by a
row_number() calculated inside a CTE, and the outer
WHERE also has
BETWEEN to facilitate paging. The row number inside the CTE is always based on the outer vote creation date, and only on the outer creation date.
As @rene correctly identified, this could lead to unpredictable results as you page through many bounties, since any two or more rows with a tie on awarded date/time (which I thought was going to be pretty rare, but here we are) could fall on the boundary of 50 per page, for example. Or row numbers are applied arbitrarily if the only column used to define them is
NULL in all rows. So, in theory, it is possible you could get the rows ordered
B on the first page, then
A on the second page, and see
A twice, and
On the other hand, the plans here are pretty stable, and the types of engine changes that lead to differences here are rarely introduced (and we don't exactly upgrade to every CU the day it is released), so the likelihood of that seems pretty small.
- Sorting could be amended by a tie-breaker as a secondary ordering column, to make sorting predictable.
Active case could be adjusted so that the row numbers were determined by a non-null column (though this wouldn't be necessary in the case of (1)).
I wasn't here, but I would bet that at the time of implementation, the arguments against these were:
- "How could there possibly be a tie in awarded date?" Honestly I don't know how the OP managed that except that if they were all picked up and awarded as some system sweep that maybe didn't exist, or was slower at the time, or just nobody thought to try to simulate that scenario?
- This one is simpler: you can only have 3 bounties at a time, so probably, "We don't need to introduce additional performance penalties of sorting on the active tab, because paging isn't needed and how many people have more than one at a time anyway?"
Knowing what we know today, we would potentially make different decisions, though due to plan stability I'm generally against adding secondary sort columns just for predictability of ordering. It's essentially, to me: "I want this query to be slightly worse all the time for no possible gain even when there's a tie." And in the case of pagination, there is so much else that can make things inconsistent and bump the order or the exact rows that belong on a specific page as you navigate (new bounty gets added, a bounty gets awarded, etc.), the value of perfection is non-existent IMHO.