Trying to find the earliest entry in my SQlite database by searching for Min(DateTime) but can’t figure out the sql command. Any help would be awesome.
DateTime is formatted as Xojo.Core.Date
[code]// Clear the ListBox
ListBox2.DeleteAllRows
// Get earliest date from the table.
Dim sql As String
//STUCK HERE
sql = "SELECT MIN(DateTime) FROM Sensor_Readings "
Dim data As RecordSet
data = App.DB.SQLSelect(sql)
While Not data.EOF
ListBox2.AddRow(data.Field(“SN”).StringValue, _
data.Field(“DateTime”).StringValue, _
data.Field(“Temp”).StringValue, _
data.Field(“PSI”).StringValue, _
Wend
data.Close[/code]
but ORDER BY Datetime will only work properly IF the data field is in a sortable format (totalseconds, or SQLDATE)
if you have it like “Jan 25,2018” and “Dec 2,2018” they will NOT be returned in proper order
Thanks Tim, you made me learn something (more) today
Indeed RowId is only garanteed to be “in increasing order” if a column in the table is declared as INTEGER PRIMARY KEY AUTOINCREMENT. And if this is the case you can directly use this column to sort your datas.
[quote=378756:@Renaud GOMBERT WEBPORTAGE]Thanks Tim, you made me learn something (more) today
Indeed RowId is only garanteed to be “in increasing order” if a column in the table is declared as INTEGER PRIMARY KEY AUTOINCREMENT. And if this is the case you can directly use this column to sort your datas.[/quote]
ROWID doesnt guarantee order. if ROWIDs are removed in the middle, those #smight be filled in when adding new rows. certain operations change the ROWIDs.
So if you want a UNIQUE ID that is in order of CREATION of the row, make your own column with that ID in it. More work on your side but that is the only way to guarantee it.
In “most” databases, RowID indicates the order the records were entered, but have no bearing on the content of the record.
But as Scott mentioned, there are operations that can/will alter that order, and in some cases they will be reused, and VACUUM will for sure alter them (unless they are marked as the PK as well). So RowID at best identifies a particular row at an Instant in time,
We might as well wait until the OP tells us how his data is SAVED to the db. His original post, as noted by Markus, doesn’t really tell us that. If it’s saved to the db as SQLDateTime (d.ToText in this case, using the New Framework as he indicates), then he can simply do what Renaud suggested:
SELECT * FROM Sensor_Readings ORDER BY DateTime ASC LIMIT 1
But until we hear how the data is saved, not much we can do here.
In my case the RowID would probably work for the search because the records are created as the time increments (data logging sensors) but I understand the pitfalls and won’t use that. Since my date format is stored as Year:Month:Day:Hour:Min:Sec then searching by date is the best search for me.
My problem is grasping the sql commands and understanding the sqlite.org website.
As Ralph said… not a good place to “learn”… but a good reference once you know what things mean and how they work.
SQL is one of those “Ah Ha!” things… you will read about it, play with it, and then all of a sudden you go “Ah Ha!” and it only then starts to make sense… Lucky for me that moment came years ago
I wasn’t clear, I meant I will keep searching for a better place to learn sqlite.
BTW, this is the best forum ever. I have always been an avid “search and read” user of forums but not really contributed much. You guys are all so helpful, thanks again.
Do not concentrate on learning SQLite … Learn “SQL”… the concepts are nearly identical across all DB engines. but SQLite has its quirks (as in it doesn’t really have “datatypes” like other DB’s)… but if you learn SQL based on what SQLite does or doesn’t do, then you will have major issues in the future if you decide to change, or need to “scale up”…