How dynamically use LIMIT in ClickHouse db?-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

I 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 │
└────────┘