no partitioned Rank in SQLite: need a Emulation

i want to rank a day, time table in sqlite3 and struggle with the absence of some analytical functions. The following table

Day        | Time
--------------------------------
2014-10-27 | 2014-10-27 08:46:48
2014-10-28 | 2014-10-28 08:02:42
2014-10-30 | 2014-10-29 08:35:11
2014-10-30 | 2014-10-30 08:20:35
2014-10-31 | 2014-10-31 08:18:13
2014-10-31 | 2014-10-31 18:12:06
2014-11-03 | 2014-11-03 10:35:45
2014-11-03 | 2014-11-04 08:26:26
2014-11-03 | 2014-11-05 08:13:15
2014-11-03 | 2014-11-07 08:06:32

i want to have a result, with an ascending rank of the colum Time partitioned by Day similar like:

Day        | Time                |Rank
-----------+---------------------+-----
2014-10-27 | 2014-10-27 08:46:48 | 1
2014-10-28 | 2014-10-28 08:02:42 | 1
2014-10-30 | 2014-10-29 08:35:11 | 2
2014-10-30 | 2014-10-30 08:20:35 | 1
2014-10-31 | 2014-10-31 08:18:13 | 2
2014-10-31 | 2014-10-31 18:12:06 | 1
2014-11-03 | 2014-11-03 10:35:45 | 4
2014-11-03 | 2014-11-04 08:26:26 | 3
2014-11-03 | 2014-11-05 08:13:15 | 2
2014-11-03 | 2014-11-07 08:06:32 | 1

i was testing some ideas for SQLite Rank i found here like

select p1.*, 
  ( select count(*) 
     from table as p2
    where p2.time> p1.time
    ) as timeRank
from table as p1

but they are mostly for ordered Rank which was not working for me.

i have a sample on SQL Fiddle, where i was try to solve the issue.

is there a way to solve this problem in sqlite or it's better to handle this in Perl/Python?s

Solution

You are close. You need a condition on the date and to use >= for the comparison:

select p1.*, 
       (select count(*) 
        from table as p2
        where p2.date = p1.date and
             p2.time >= p1.time
       ) as timeRank
from table as p1;

Strictly speaking, this is closer to a dense_rank(), but the difference is immaterial unless you have duplicate values. If you have ties, then the following should work:

select p1.*, 
       (select 1 + count(*) 
        from table as p2
        where p2.date = p1.date and
             p2.time > p1.time
       ) as timeRank
from table as p1;