No split rank in SQLite: emulation needed
I want to rank day, time table in sqlite3 and struggle with missing some analytic functionality. 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 get a result with ascending colum rank Time divided by Day, similar to:
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 tested some ideas for SQLite Rank i found here as
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 didn't work for me.
I have a sample on SQL Fiddle where I was trying to solve the problem.
Is there a way to solve this problem in sqlite or is it better to deal with it in Perl / Python? s
source to share
You are close. You need a date condition and use >=
for 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 connections, 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;
source to share