Clickhouse: Can you add row numbers in a result set ?

[healiseu]

Hi, I am trying to find a way to format the result set from an SQL query by adding a row number for each tuple. I looked at the various output formats and at the functions section but I could not find something really suitable. There is this numbers(N) but it returns a table with a single 'number' column.

The reason I am asking this is because I want to output the result set from Clickhouse to a CSV file with the first column to have row numbers.

[blinkov]

Try https://clickhouse.yandex/docs/en/query_language/functions/other_functions/#rownumberinallblocks , maybe it'll work fine for your usecase.

[alexey-milovidov]

There is rowNumberInAllBlocks function.

It returns the row numbers for data it processes, and the output depends on a context this function is executed. To ensure proper context, you can wrap your query in a subquery:

SELECT *, rowNumberInAllBlocks() FROM (SELECT ...)

[blinkov]

@healiseu try the trick suggested by Alexey

[healiseu]

Got it, thank you @alexey-milovidov and @blinkov

SELECT 
    rowNumberInAllBlocks() AS rowNo, 
    *
FROM 
(
    SELECT 
        trip_pk, 
        trip_srt_station_id
    FROM BicycleTripFlat 
    ORDER BY trip_srt_station_id ASC
    LIMIT 10
) 
FORMAT TSVWithNames

and output is

rowNo   trip_pk trip_srt_station_id
0   527893  31000
1   582428  31000
2   665320  31000
3   307444  31000
4   1210888 31000
5   524105  31000
6   584401  31000
7   658881  31000
8   307567  31000
9   1214093 31000