ClickHouse DB::Exception: WITH TIES requires ORDER BY

The "DB::Exception: WITH TIES requires ORDER BY" error in ClickHouse is raised when a query uses LIMIT ... WITH TIES but does not include an ORDER BY clause. The error code is WITH_TIES_WITHOUT_ORDER_BY. The WITH TIES modifier tells ClickHouse to include all rows that are "tied" with the last row in the LIMIT set according to the sort order, so without an ORDER BY there is no defined ordering to determine which rows are tied.

Impact

This error causes the query to fail before execution begins. Effects include:

• Immediate query failure with no results returned
• Blocked reporting or application queries that were written with WITH TIES
• Confusion for users migrating SQL from other databases where behavior may differ

Common Causes

1. Omitted ORDER BY clause -- The most straightforward cause: the query has LIMIT N WITH TIES but no ORDER BY at all.
2. ORDER BY removed during query refactoring -- A query that originally had ORDER BY was modified, and the ORDER BY was dropped while WITH TIES was left in place.
3. Copy-paste errors -- Partial query construction from templates or examples where the ORDER BY portion was not included.
4. Dynamic query generation -- Application code that conditionally adds ORDER BY failed to include it while still appending WITH TIES to the LIMIT clause.

Troubleshooting and Resolution Steps

1. Add an ORDER BY clause to the query:

   -- Failing query
   SELECT user_id, score FROM leaderboard LIMIT 10 WITH TIES;
   
   -- Fixed query
   SELECT user_id, score FROM leaderboard ORDER BY score DESC LIMIT 10 WITH TIES;
   

   The ORDER BY defines the sort order used to determine ties.

2. Remove WITH TIES if ordering is not needed: If you simply want a fixed number of rows without tie-breaking logic:

   SELECT user_id, score FROM leaderboard LIMIT 10;
   

3. Check dynamic query builders: If your application constructs queries programmatically, ensure WITH TIES is only appended when an ORDER BY clause is also present:

   query = "SELECT user_id, score FROM leaderboard"
   if order_by:
       query += f" ORDER BY {order_by}"
       if with_ties:
           query += " WITH TIES"
   query += f" LIMIT {limit}"
   

   Note that in ClickHouse, the syntax is ORDER BY ... LIMIT N WITH TIES -- the WITH TIES comes after the LIMIT value.

4. Understand what WITH TIES does before using it: WITH TIES extends the result set beyond the LIMIT count to include all rows that share the same ORDER BY values as the last included row:

   -- If scores are: 100, 95, 90, 90, 90, 85, 80
   -- LIMIT 3 returns: 100, 95, 90
   -- LIMIT 3 WITH TIES returns: 100, 95, 90, 90, 90
   SELECT user_id, score
   FROM leaderboard
   ORDER BY score DESC
   LIMIT 3 WITH TIES;
   

Best Practices

• Only use WITH TIES when you have a clear requirement to include tied rows -- for most queries, a plain LIMIT is sufficient.
• Always pair WITH TIES with a well-defined ORDER BY that reflects the business logic for tie determination.
• Be aware that WITH TIES can return more rows than the specified LIMIT value, which may affect downstream processing or memory usage.
• In application code, treat WITH TIES and ORDER BY as a coupled pair -- never add one without the other.

Frequently Asked Questions

Q: Can I use WITH TIES with LIMIT ... OFFSET?
A: Yes, ClickHouse supports combining OFFSET with LIMIT ... WITH TIES. The offset is applied first, then the LIMIT WITH TIES logic applies to the remaining rows. An ORDER BY clause is still required.

Q: Does WITH TIES work with LIMIT BY?
A: No, WITH TIES is a modifier for the standard LIMIT clause only. LIMIT BY is a separate ClickHouse-specific feature for limiting rows per group and does not support the WITH TIES modifier.

Q: How many extra rows can WITH TIES return?
A: There is no fixed upper bound. If many rows share the same ORDER BY values as the last row, all of them will be included. In the worst case (all rows have the same sort value), WITH TIES would return the entire result set regardless of the LIMIT value.

Q: Is WITH TIES a standard SQL feature?
A: Yes, FETCH FIRST N ROWS WITH TIES is part of the SQL standard (SQL:2008). ClickHouse implements it as LIMIT N WITH TIES, which is functionally equivalent.