want to use limit in ClickHouse db like param, which goes to me from front, and I want use limit if I receive it, or not use if not receive.
I know example in Postgresql:
SELECT * FROM table
LIMIT CASE WHEN @param > 0 THEN @param END;
But I don't know how it make in ClickHouse without concatenation strokes. Cos if I wrote LIMIT in my sql script then I need set some number, else it won't work.
SELECT * FROM table LIMIT CASE WHEN @param > 0 THEN @param END;
I want limit by condition.
First of all, a limit query should generally have an ORDER BY clause, so your starting point should be something like:
SELECT *
FROM yourTable
LIMIT CASE WHEN @param > 0 THEN @param END
ORDER BY some_col;
This also won't work though. One common workaround uses the ROW_NUMBER() window function:
SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER (ORDER BY some_col) rn
FROM yourTable
) t
WHERE @param <= 0 OR rn <= @param;
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY some_col) rn FROM yourTable ) t WHERE 100 <= 0 OR rn <= 100; I receive error: Code: 241. DB::Exception: Memory limit (total) exceeded: would use N GiBI don't see a problem
select * from numbers(10) limit case when uptime()>0 then uptime() else 1 end;
SELECT *
FROM numbers(10)
LIMIT multiIf(uptime() > 0, uptime(), 1)
Query id: f14d41af-67e4-43e5-9804-dfb5126e256b
┌─number─┐
│ 0 │
│ 1 │
│ 2 │
│ 3 │
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└────────┘
uptime() > 0 == false I don't want to use limit "1", I want receive all values from request
limit 1000000000000