A ROW_NUMBER function

[gramian]

Hi,
some SQL engines provide a ROW_NUMBER function which assigns sequential numbers to the rows in a result set, see for example https://www.postgresql.org/docs/16/functions-window.html . I think something like this can currently not be done with ArcadeDB. I propose to add a row_number function, maybe without the OVER part to avoid changing the grammar.

Is this possible as a normal SQL function, as the result set is affected after projection, unwind etc?

[lvca]

Isn't similar to the @Rid we already have?

[gramian]

ROW_NUMBER is different from the @rid, it numbers the return result set. For example SELECT row_number(), name FROM Names returns:

1, Joe 2, John 3, Jay ... 

and SELECT row_number(), name FROM Names ORDER BY name returns:

1, Jay 2, Joe 3, John ... 

So row_number numbers the rows of the query result view or table. So this function is special in the regard that it only be used at the end of the projection.

[lvca]

So it could be implemented with a new function with a number that increments. I wonder if this would work:

select $row as row, XXXX from YYY let row = row + 1

[gramian]

This surprisingly works! I always assumed the LET block is not evaluated per result.