[jupp0r]

I found it to be a generally useful rule to never "ORDER BY created" but instead "ORDER BY created,id" instead to achieve stable sorting.

I recently added some indices to a few tables to speed up a complicated query with lots of subqueries and joins and ran into many unit test failures because usage of the new indices changed the order in which items with the same "created" values were returned.

[adam-p]

Nice. That's a good general rule to follow.

[bufferoverflow]

If there's an increasing ID, just sort by ID.

[zoky]

Won’t work if your ID is a UUID. Also, this is more generally applicable to any date, not just created.

[keep_reading]

UUIDv7 is sortable by time

[zoky]

And I’m sure that will be incredibly useful in 20 years when UUIDv7 has entirely supplanted UUIDv4 in all legacy systems (and you only need to sort by date created), but for now let’s just go with the best practice for the foreseeable future and sort in a way that not only ensures consistency but future-proofs us indefinitely.

[keep_reading]

Then don't use UUIDs, use snowflakes / flakes