[ACCEPTED]-Finding the difference in rows in query using SQLite-xulrunner

Accepted answer

Score: 10

Yes, it is a bit late, but for completeness. SQLite Release 3.25.0 in 2018 added support for window functions. And 7 the above task can now be completed by using 6 the LAG() and LEAD() functions.

Taken from: https://www.sqlitetutorial.net/sqlite-window-functions/

LAG Provides access to a row at a given physical offset that comes before the current row.

So using the 5 sqlite3 command in Linux, the following 4 should match the output listed in your question. The 3 first 2 commands are only there to display 2 proper output format.

sqlite> .mode columns
sqlite> .headers on
sqlite> CREATE TABLE data(id INT, record_id TEXT, price REAL);
sqlite> INSERT INTO data VALUES(1,"apple001",36.00);
sqlite> INSERT INTO data VALUES(67,"apple001",37.87);
sqlite> INSERT INTO data VALUES(765,"apple001",45.82);
sqlite> INSERT INTO data VALUES(892,"apple001",26.76);
sqlite> SELECT id, record_id, price, (price - LAG(price, 1) OVER (ORDER BY id)) AS difference FROM data;
id          record_id   price       difference
----------  ----------  ----------  ----------
1           apple001    36.0                  
67          apple001    37.87       1.87      
765         apple001    45.82       7.95      
892         apple001    26.76       -19.06

I hope this will save 1 new users some time.

Score: 9

I do not know if there are some limitations 6 in SQLite, but you can try the following 5 statements that are working in Sql Server.

If 4 the time difference is constant (you state 3 that it is 5 minutes), you can write:

SELECT A.id, A.record_id, A.price, A.time, ISNULL(A.price - B.price, 0) AS difference
FROM Table1 as A 
    LEFT OUTER JOIN Table1 B ON A.record_id = B.record_id AND A.time - B.time = 5

otherwise

SELECT A.id, A.record_id, A.price, A.time, ISNULL(A.price - B.price, 0) AS difference
FROM Table1 as A 
    LEFT OUTER JOIN Table1 B ON B.record_id = A.record_id 
         AND B.time = (SELECT MAX(time) FROM Table1 C WHERE C.time < A.time AND C.record_id = A.record_id)

A 2 statement without joins is the following

SELECT id, record_id, price, time,
    (SELECT A.price - B.price
        FROM Table1 as B
        WHERE B.record_id = A.record_id AND
            B.time = (SELECT MAX(time) FROM Table1 C WHERE C.time < A.time AND C.record_id = A.record_id)) AS difference
FROM Table1 as A

I 1 hope that one of them will help you.

Source: stackoverflow.com

[ACCEPTED]-Finding the difference in rows in query using SQLite-xulrunner

Postgres LIMIT/OFFSET strange behaviour

How to programmatically limit bandwidth usage of my c# application?

Is there any length limit for value kept in properties file?

What is the Equivalent syntax of mysql " LIMIT " clause in SQL Server

How to use regex lookahead to limit the total length of input string

How to specify limit and offset in array in rails?

VBA String Limit

Use LIMIT to paginate results in MySQL query

Error while using PDO prepared statements and LIMIT in query

Java 8 Stream: difference between limit() and skip()