How to mimic a ROW_NUMBER() functionality without using ROW_NUMBER()?

Question

I've been running into this problem on an exercise and it's driving me crazy that I can't fix it... Basically I'm using SQLLite so I'm limited to using any window functions to get a result and the more painful part is that there is no unique id on the already created schema. See example:

**Name,  DateAdded,  Amount**
John,  2015-10-12, 100.00
John,  2015-10-15, 50.00
Aaron, 2015-09-20, 10.00
Paul,  2014-12-20, 24.00
Paul,  2015-12-23, 32.00

What is the alternative to using ROW_NUMBER() in SQL Lite when you don't have any unique id to go by ? I'd like to partition by the name and order by amount.

Thanks in advance.

Answer 1

This is all kinds of ugly, but with no windowing functions this is the only way I know of to mimic the row_number function:

select
  t.Name, t.DateAdded, t.Amount,
  ( select count (*)
     from MyTable u
     where
       t.Name = u.Name and
       t.DateAdded >= u.DateAdded
  ) as row_number
from MyTable t

Results:

John    2015-10-12  100 1
John    2015-10-15  50  2
Aaron   2015-09-20  10  1
Paul    2014-12-20  24  1
Paul    2014-12-23  32  2