ClickHouse / ClickHouse/ClickHouse

LIMIT n[,m] BY x WITH TIES

Open
#47,817 0 comments 0 reactions 0 assignees View on GitHub
comp-query-execution external feature minor
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

**Use case**

Support for WITH TIES keyword for LIMIT BY

**Describe the solution you'd like**

```
SELECT *
FROM
(
SELECT
arrayJoin(['id_a', 'id_b']) AS id,
intDiv(number, 3) AS value
FROM numbers(10)
)
WHERE id = 'id_a'
ORDER BY
id ASC,
value DESC
LIMIT 2
WITH TIES

┌─id───┬─value─┐
│ id_a │ 3 │
│ id_a │ 2 │
│ id_a │ 2 │
│ id_a │ 2 │
└──────┴───────┘

SELECT *
FROM
(
SELECT
arrayJoin(['id_a', 'id_b']) AS id,
intDiv(number, 3) AS value
FROM numbers(10)
)
ORDER BY
id ASC,
value DESC
LIMIT 2 BY id

┌─id───┬─value─┐
│ id_a │ 3 │
│ id_a │ 2 │
│ id_a │ 2 │
│ id_a │ 2 │
│ id_b │ 3 │
│ id_b │ 2 │
│ id_b │ 2 │
│ id_b │ 2 │
└──────┴───────┘

```

**Describe alternatives you've considered**

```
SELECT *
FROM
(
WITH dense_rank() OVER (PARTITION BY id ORDER BY value DESC) AS row
SELECT
row,
*
FROM
(
SELECT
arrayJoin(['id_a', 'id_b']) AS id,
intDiv(number, 3) AS value
FROM numbers(10)
)
LIMIT 10000
)
WHERE row <= 2

Query id: 92e4647f-e90f-4564-8e30-db1c0baea8d2

┌─row─┬─id───┬─value─┐
│ 1 │ id_a │ 3 │
│ 2 │ id_a │ 2 │
│ 2 │ id_a │ 2 │
│ 2 │ id_a │ 2 │
│ 1 │ id_b │ 3 │
│ 2 │ id_b │ 2 │
│ 2 │ id_b │ 2 │
│ 2 │ id_b │ 2 │
└─────┴──────┴───────┘
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.