ClickHouse / ClickHouse/ClickHouse
LIMIT n[,m] BY x WITH TIES
- 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
Assessment
This issue has not been assessed yet.