ClickHouse DB::Exception: LIMIT BY WITH TIES is not supported

The "DB::Exception: LIMIT BY WITH TIES is not supported" error in ClickHouse occurs when a query attempts to combine the LIMIT BY clause with the WITH TIES modifier. The LIMIT_BY_WITH_TIES_IS_NOT_SUPPORTED error indicates that these two features are incompatible and cannot be used together in the same query.

Impact

The query fails at the parsing/validation stage. No data is processed or returned. This is a query syntax issue that requires rewriting the query to avoid combining these two clauses.

Common Causes

1. Writing a query that uses both LIMIT N BY column and WITH TIES
2. An ORM or query builder that independently adds LIMIT BY and WITH TIES clauses
3. Migrating a query from another database that supports this combination
4. Misunderstanding the semantics of LIMIT BY vs standard LIMIT ... WITH TIES

Troubleshooting and Resolution Steps

1. Understand the difference between LIMIT BY and LIMIT WITH TIES:

   -- LIMIT BY: limits rows per group (ClickHouse-specific)
   SELECT * FROM my_table ORDER BY date LIMIT 3 BY user_id;
   -- Returns at most 3 rows for each distinct user_id
   
   -- LIMIT WITH TIES: includes ties with the last row
   SELECT * FROM my_table ORDER BY score DESC LIMIT 10 WITH TIES;
   -- Returns top 10 plus any rows tied with the 10th row's score
   

2. Remove WITH TIES and use LIMIT BY alone:

   -- Instead of combining both:
   -- SELECT * FROM t ORDER BY score LIMIT 3 BY group WITH TIES;  -- Error!
   
   -- Use LIMIT BY without WITH TIES:
   SELECT * FROM my_table ORDER BY score DESC LIMIT 3 BY group_id;
   

3. Or use LIMIT WITH TIES without LIMIT BY:

   SELECT * FROM my_table ORDER BY score DESC LIMIT 10 WITH TIES;
   

4. Emulate the combined behavior with a subquery:

   -- If you need per-group limiting with ties, use a window function approach
   SELECT * FROM (
       SELECT *,
           row_number() OVER (PARTITION BY group_id ORDER BY score DESC) AS rn,
           dense_rank() OVER (PARTITION BY group_id ORDER BY score DESC) AS dr
       FROM my_table
   ) WHERE dr <= 3;
   -- This gives the top 3 distinct score levels per group, including ties
   

5. Use RANK or DENSE_RANK window functions for tie-aware limiting:

   SELECT * FROM (
       SELECT *, rank() OVER (PARTITION BY group_id ORDER BY score DESC) AS rnk
       FROM my_table
   ) WHERE rnk <= 3;
   

Best Practices

• Use LIMIT BY when you need a fixed number of rows per group and do not need tie handling.
• Use LIMIT ... WITH TIES when you need to include tied rows at the boundary of a global limit.
• For per-group limiting with tie awareness, use window functions (RANK, DENSE_RANK) which give you full control over tie-breaking behavior.
• Document which limiting strategy is appropriate for each query to avoid confusion.

Frequently Asked Questions

Q: Why can't LIMIT BY and WITH TIES be used together?
A: LIMIT BY and WITH TIES operate at different semantic levels. LIMIT BY partitions the result by column values and limits within each partition, while WITH TIES extends the limit to include rows tied with the boundary row. Combining them creates ambiguous semantics that ClickHouse does not attempt to resolve.

Q: Is LIMIT BY a standard SQL feature?
A: No. LIMIT BY is a ClickHouse-specific extension. Standard SQL achieves similar functionality using window functions with ROW_NUMBER(), RANK(), or DENSE_RANK().

Q: Can I get top-N per group with ties in ClickHouse?
A: Yes, use window functions. RANK() OVER (PARTITION BY group ORDER BY value) gives you per-group ranking with tie awareness. Filter the result to keep only ranks within your desired limit.