CODEWITHSUNDEEP
sqlite
Promap
Promap

Reputation: 1

Increment values based on variable

I have a table that looks like this :

DATE         user_id  amount_spent
2018-04-16    xxxx     0.99
2018-05-16    xcxc     5.10
2018-05-18    xdxd     4.99
2018-04-17    xvxv     7.00
...

I need to add another column called ID that ranges for 1 to n and that indicates, per user, the position of the purchase. 1 being the order of the 1st purchase and n the order of the last one.

So it would output something like that : 

DATE         user_id  amount_spent   ID
2018-04-16    xxxx     0.99          1
2018-05-16    xcxc     5.10          2
2018-05-18    xdxd     4.99          4
2018-04-17    xvxv     7.00          3
...

So my code looks like this atm : 

    ALTER TABLE temp_table
    ADD ID VARCHAR;

  UPDATE temp_table SET ID = (SELECT * FROM (WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x< (select count(*) from temp_table))
SELECT x FROM cnt));

But then the ID isn't associated with the date and it only outputs 1 for every ID

Any help would be appreciated, thanks !

Upvotes: 0

Views: 74

Answers (1)

CL.
CL.

Reputation: 180080

Use a correlated subquery to count how many rows have a smaller date value:

UPDATE temp_table
SET ID = (SELECT count(*)
          FROM temp_table AS t2
          WHERE t2.DATE <= temp_table.DATE);

Alternatively, insert the rows into the temporary table with the correct ORDER BY, then the rowid values are correct.

Upvotes: 1

Related Questions