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.