4 ms·
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
by jupp0r 3y ago
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.
- bufferoverflow 3y agoIf there's an increasing ID, just sort by ID.
- zoky 3y agoWon’t work if your ID is a UUID. Also, this is more generally applicable to any date, not just created.
- keep_reading 3y agoUUIDv7 is sortable by time
- zoky 3y agoAnd 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 3y agoThen don't use UUIDs, use snowflakes / flakes
- adam-p 3y agoNice. That's a good general rule to follow.