How to do pagination in clickhouse

Question

Can you please suggest how can I do pagination in click house? Dor example in elastic search I do aggregation query like below. Here elastic search takes parameters partition number and partition size and give the result. Let's say in total we have 100 records than if we give partition size of 10 and partition number 2 then we will get 11-20 latest records.

How can we do it in click house considering data in inserting in a table.

SearchResponse response = elasticClient.prepareSearch(index)
    .setTypes(documentType)
    .setQuery(boolQueryBuilder)
    .setSize(0)
    .addAggregation(AggregationBuilders.terms("unique_uids")
    .field(Constants.UID_NAME)
    .includeExclude(new IncludeExclude(partition,numPartitions))
    .size(Integer.MAX_VALUE))
    .get();

Answer 1

According to specification common sql syntax for limit and offset will work:

LIMIT n, m allows you to select the first m rows from the result after skipping the first n rows. The LIMIT m OFFSET n syntax is also supported.

https://clickhouse.yandex/docs/en/query_language/select/#limit-clause