SQLITE Query question

General
1

I have a table with UID and DATETIME

Every record there is supposed to be inserted sequentially on a later date, and obviously a growing ID.

How do I check with a query that all the dates are ordered and there is not a a date lower/smaller than the former row ?

Thanks
R

2

find any row where its data is < the previous rows date
the previous row should be the row whose rowid is > all other but less than this row (unless you’rs got out of whack somehow)
effectively

select rowid, UID from table where 
         table.date < (previous rows date)

ok so … previous rows date … since the rowids are NOT necessarily guaranteed to be sequential

something along the lines of (note TABLE is a generic name since I dont know what your table is named)

select rowid, UID from table THISROWTABLE where 
         table.date < ( select date from table PREVIOUSROWSTABLE 
                                         where PREVIOUSROWSTABLE.rowid < THISROWTABLE.rowid
                                             and rowid = ( select(max(rowid) from table MAXROWID 
                                                              where MAXROWID.rowid < PREVIOUSROWSTABLE.rowid) )

should find rows where that criteria doesnt hold true

3

Not sure I follow you Norman…

(i’ll give you a bit more info, in case it helps)

Table name is cash. I want to be sure no one altered the date of the pc to insert records in former dates (there was a bug that would not take the timestamp from the server BUT from the local pc), shame on me.
So UID is sequential as it is an autoincrement column.

In your example,
What’s previous rows date and PREVIOUSROWTABLE ?

4 
select rowid, UID from cash THISROWTABLE where 
         table.date <= ( select date from cash PREVIOUSROWSTABLE 
                                         where PREVIOUSROWSTABLE.rowid < THISROWTABLE.rowid
                                             and rowid = ( select(max(rowid) from cash MAXROWID 
                                                              where MAXROWID.rowid < PREVIOUSROWSTABLE.rowid) )

ok so the outer part is to find all those rows where this rows date is less than the previous rows date

select rowid, UID from cash THISROWTABLE where table.date < ... and here we need to fin the previous rows date .......

the first inner part is to grab the date from “the previous row”

[code]( select date from cash PREVIOUSROWSTABLE
where PREVIOUSROWSTABLE.rowid < THISROWTABLE.rowid [ /code]
BUT in order to be able to identify the “previous row” we need to be able to use the outer parts “current row”
To do that we need to be able to unabiguously refer to the outer table - thats where the THISROWTABLE is required
it lets use refer to the row that the outer table is examing inside this inner query to compare rowids

and “the previous row” is the one that has the largest row id that is less than the outer most rows id

to find that max that is just less than the outer one we need yet another nested query that will find us that max rowid that is JUST less than the row we are currently looking at in the outer

I hope that makes sense (and fwiw I might now have the quest exactly right but the style is or should be really close based on what I know at this time )

5

I made it work. Thanks Norman!