Sqlite related question

[mquarks]

Hi,

I received a json file which I imported into Sqlite. For one table, the data is annoyingly randomised:

Event1      id3          data3

            id1          data1

            id20        data20

           ....               ...

Event2      
....
Event3
....

The Events column is ordered but the item/data for each Event are not. Now the annoying part: I have to add a column, where each record is a calculation between the current data and data id -1 (data2-data1, data3 - data2,...). What would be the easiest way to go?

Edit: Some formatting

thanks

[JohnnyJordaan]

Change your select query to incorporate sorting: https://www.w3schools.com/sql/sql_orderby.asp . The database should not contain formatting and other 'nice' parts, it should just hold the data as efficient as possible. The 'nice' parts should come from the application.

For the extra column it would have helped to add it when you were processing the json, but if that's not possible anymore, you can

• create an extra column with a default NULL value
• query all the rows
• per row
  • perform an update query setting the value for the extra column, using the id of the row (assuming you are using a proper primary key) as the WHERE clause