_count change the order of the results
why does this happen? same queries but one has a _count inside the select clause and the order of the results are different, is this supposed to be like this?
3 Replies
You've gone full bot mode! Let me fetch that AI answer for you ASAP.
When you add a
_count (or any aggregate) to your Prisma query, it can affect the order of the results, especially if you use it in the orderBy clause. This is expected behavior in both Prisma and SQL databases.
If you order by a non-unique value like _count, and multiple records have the same count, the database does not guarantee a stable order among those records unless you specify a secondary, unique tiebreaker (like an id). This can lead to the same query returning different orders for records with identical _count values, or even to issues like repeated or missing items in paginated results. The recommended solution is to add a second, unique field to your orderBy to ensure stable and predictable ordering:
This ensures that records with the same count are consistently ordered by id as a tiebreaker, preventing unexpected result order or pagination issues. This is not a bug, but standard SQL behavior, and Prisma follows this pattern. If you only order by _count, the order among ties is not guaranteed and may change between queries or pages Items repeat in query with pagination and orderBy.
If you observe different results when only adding _count to your query (without using it in orderBy), and the queries are otherwise identical, this would be unexpected. However, the knowledge sources do not mention such a case—ordering differences are always tied to the use of _count in orderBy. If you have a specific example where just selecting _count (not ordering by it) changes the order, it may be worth opening an issue with a reproducible example.
🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly.
In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.Could you provide an example of this behaviour?
Is it happening in latest prisma version?
Hello! a bit late but from what I remember adding any clause will change the order of the results when doing the same query twice.
With twice in mean in parallel