ClickHouse limit with ties

clickhouse_limit_with_ties.sql

eb90336ba886 :) select * from (select number, intDiv(number,5) value from numbers(20)) order by value;

SELECT *
FROM 
(
    SELECT 
        number, 
        intDiv(number, 5) AS value
    FROM numbers(20)
)
ORDER BY value ASC

┌─number─┬─value─┐
│      0 │     0 │
│      1 │     0 │
│      2 │     0 │
│      3 │     0 │
│      4 │     0 │
│      5 │     1 │
│      6 │     1 │
│      7 │     1 │
│      8 │     1 │
│      9 │     1 │
│     10 │     2 │
│     11 │     2 │
│     12 │     2 │
│     13 │     2 │
│     14 │     2 │
│     15 │     3 │
│     16 │     3 │
│     17 │     3 │
│     18 │     3 │
│     19 │     3 │
└────────┴───────┘

20 rows in set. Elapsed: 0.004 sec. 





eb90336ba886 :) select * from (select number, intDiv(number,5) value from numbers(20)) order by value limit 1;

SELECT *
FROM 
(
    SELECT 
        number, 
        intDiv(number, 5) AS value
    FROM numbers(20)
)
ORDER BY value ASC
LIMIT 1

┌─number─┬─value─┐
│      0 │     0 │
└────────┴───────┘

1 rows in set. Elapsed: 0.002 sec. 



---- and now - with ties!




eb90336ba886 :) select * from (select number, intDiv(number,5) value from numbers(20)) order by value limit 1 with ties;

SELECT *
FROM 
(
    SELECT 
        number, 
        intDiv(number, 5) AS value
    FROM numbers(20)
)
ORDER BY value ASC
LIMIT 1
 WITH TIES

┌─number─┬─value─┐
│      0 │     0 │
│      1 │     0 │
│      2 │     0 │
│      3 │     0 │
│      4 │     0 │
└────────┴───────┘

5 rows in set. Elapsed: 0.005 sec. 


---

eb90336ba886 :) select * from (select number, intDiv(number,5) value from numbers(20)) order by value limit 4 with ties;

SELECT *
FROM 
(
    SELECT 
        number, 
        intDiv(number, 5) AS value
    FROM numbers(20)
)
ORDER BY value ASC
LIMIT 4
 WITH TIES

┌─number─┬─value─┐
│      0 │     0 │
│      1 │     0 │
│      2 │     0 │
│      3 │     0 │
│      4 │     0 │
└────────┴───────┘

5 rows in set. Elapsed: 0.005 sec. 


---

SELECT *
FROM 
(
    SELECT 
        number, 
        intDiv(number, 5) AS value
    FROM numbers(20)
)
ORDER BY value ASC
LIMIT 6
 WITH TIES

┌─number─┬─value─┐
│      0 │     0 │
│      1 │     0 │
│      2 │     0 │
│      3 │     0 │
│      4 │     0 │
│      5 │     1 │
│      6 │     1 │
│      7 │     1 │
│      8 │     1 │
│      9 │     1 │
└────────┴───────┘

10 rows in set. Elapsed: 0.005 sec. 

-- desc works too

eb90336ba886 :) select * from (select number, intDiv(number,5) value from numbers(20)) order by value desc limit 1 with ties;

SELECT *
FROM 
(
    SELECT 
        number, 
        intDiv(number, 5) AS value
    FROM numbers(20)
)
ORDER BY value DESC
LIMIT 1
 WITH TIES

┌─number─┬─value─┐
│     15 │     3 │
│     16 │     3 │
│     17 │     3 │
│     18 │     3 │
│     19 │     3 │
└────────┴───────┘

5 rows in set. Elapsed: 0.005 sec.