I tried the examples from here:
https://www.sqlitetutorial.net/sqlite-max/
..comes down to
Code: Select all
SELECT id, MAX(date) FROM tbl GROUP BY id
or
Code: Select all
SELECT id as id1, date FROM tbl WHERE date = (SELECT MAX(date) FROM tbl WHERE id = id1)
Code: Select all
UseSQLiteDatabase()
Procedure CheckDatabaseUpdate(Database, Query$)
Result = DatabaseUpdate(Database, Query$)
If Result = 0
Debug DatabaseError()
EndIf
ProcedureReturn Result
EndProcedure
; DatabaseFile$ = GetTemporaryDirectory()+"Database.sqlite"
;
; If CreateFile(0, DatabaseFile$)
; CloseFile(0)
DatabaseFile$ = ":memory:"
If OpenDatabase(0, DatabaseFile$, "", "")
CheckDatabaseUpdate(0, "CREATE TABLE tbl (id int, date date)")
CheckDatabaseUpdate(0, "INSERT INTO tbl (id, date) VALUES ('1', '2020-01-12')")
CheckDatabaseUpdate(0, "INSERT INTO tbl (id, date) VALUES ('1', '2020-01-13')")
CheckDatabaseUpdate(0, "INSERT INTO tbl (id, date) VALUES ('1', '2020-01-18')")
CheckDatabaseUpdate(0, "INSERT INTO tbl (id, date) VALUES ('2', '2020-01-13')")
CheckDatabaseUpdate(0, "INSERT INTO tbl (id, date) VALUES ('2', '2020-01-16')")
CheckDatabaseUpdate(0, "INSERT INTO tbl (id, date) VALUES ('2', '2020-01-21')")
q.s = ""
q + "SELECT id, MAX(date) FROM tbl GROUP BY id"
;q + "SELECT id as id1, date FROM tbl WHERE date = (SELECT MAX(date) FROM tbl WHERE id = id1)"
If DatabaseQuery(0, q)
While NextDatabaseRow(0)
Debug GetDatabaseString(0, 0)
Debug GetDatabaseString(0, 1)
Debug ""
Wend
FinishDatabaseQuery(0)
Else
Debug DatabaseError()
EndIf
CloseDatabase(0)
Else
Debug "Can't open database !"
EndIf
; Else
; Debug "Can't create the database file !"
; EndIf