how to use ROW_NUMBER of T-SQL in SQLite with group by query?

Question

I've already checked this: How to use ROW_NUMBER in sqlite

But it's not helpful for me. I've query like this & it returns me false row index. What's wrong with the query?

SELECT (select count(*) from medicalrecords b where a.id >= b.id ) as cnt,avg(bmi)
FROM MedicalRecords a 
WHERE Date BETWEEN datetime('now', '-1 month') AND datetime('now', 'localtime') 
group by strftime('%W', Date)

SQL Fiddle

What does 'false row index' mean? What did you expect and what did you get? — Panagiotis Kanavos, Oct 18 '13 at 08:54
I expect 1, 2, 3, ... but I am getting not those values instead I get some random numbers. — Farhan Ghumra, Oct 18 '13 at 09:20

score 0 · Answer 1 · answered Oct 18 '13 at 12:57

This can be done in a single query, but using VIEW will become much easier:

-- Groups (without sequencial):
CREATE VIEW Grp0 AS SELECT DISTINCT STRFTIME('%W', Date) AS Week FROM MedicalRecords WHERE Date BETWEEN DATETIME('NOW', '-1 month') AND DATETIME('now', 'localtime');

-- Groups (with sequencial):
CREATE VIEW Grp AS SELECT (SELECT COUNT() FROM Grp0 AS _ WHERE Week<=Grp0.Week), Week FROM Grp0;

-- Query:
SELECT Week, (SELECT AVG(bmi) FROM MedicalRecords WHERE STRFTIME('%W', Date) = Week)) FROM Grp;

how to use ROW_NUMBER of T-SQL in SQLite with group by query?

SQL Fiddle

1 Answers1

Linked