Ranking on same data in SQL Lite

I have table as below

CREATE TABLE test(
   ID INT
);

With Following data

Sample Data

I need following result

ID    Rank
100   1
100   2
105   1
105   2
105   3

I only need to use normal SQL Query no special functions


ANSWERS:


You can use rowid in a correlated query to achieve the result. Since, rowid uniquely identifies as row, we can use it in our case to do correlated counting to assign row number.

Try this:

select id, (
        select count(*)
        from test t2
        where t.id = t2.id
            and t2.rowid <= t.rowid
        ) rank
from test t;


 MORE:


 ? Removing duplicated rows with UNION
 ? SQL - logic behind exists and not exists in multiple dependent sub queries
 ? SQL statement not returning exact results expected
 ? SQL code not running properly for joining tables with subquery
 ? Display unique SQL records joined to other data
 ? SQL date conversion to written date
 ? get one/fist row per hour for the actual day
 ? JQuery/Javascript - Multiple Variable Names
 ? Schema, Owner for objects in MS SQL
 ? Storing video duration time in sql server