Add ROWNUM() function

[agershun]

Suppose i have a table without a unique id. For example

    SELECT some_number, some_text FROM table;

some_number some_text

    42          "Test"
    84          "Another Test"

Now i want Alasql to additionally return the rownumber of every tuple:

    SELECT ROWNUM(), some_number, some_text FROM table;

    rownum some_number some_text
    0      42          "Test"
    1      84          "Another Test"

Do have a suggestion for me how to solve this issue?

[agershun]

Currently, there is no such function in Alasql.

The simplest way to solve this is to add a JavaScript lines after SELECT:

    var res = alasql('SELECT some_number, some_text FROM tabl');
    res.forEach(function(d,idx){d.rownum = idx});

Unfortunately, ROWNUM() is not a function, but rather pseudo-function, because SQL server has to process it after ORDER BY clause.

[agershun]

T-SQL uses ROW_NUMBER() name and Orcale uses ROWNUM() for this functions.

[mathiasrw]

gosh.... would be so awesome if one could select the parser to match the favorite languarge flavour

[agershun]

For a now, these functions can be combined in one (it does not cost too much to have additional alias for this function). But for the future, it is a good idea to have them separate (may be to have the option flag like alasql.dialect = 'TSQL'

[agershun]

Done. Will be available in version 0.1.6 soon or now in the develop branch.

alasql('CREATE TABLE one (a INT PRIMARY KEY)');

for(var i=1;i<1000;i++) {
   alasql('INSERT INTO one VALUES (?)',[i]);
};

var res = alasql('SELECT * \
    FROM (SELECT a, ROWNUM() AS r FROM one)\
    WHERE r BETWEEN 55 AND 60');

You can try this example in jsFiddle

[abneralvarado]

Thanks!