PureBasic Forums - English

Posted: **Thu Jan 23, 2020 7:34 pm**

I have a SQLite db with a table containing the values:

id                  date
1                   2020-01-12
1                   2020-01-13
1                   2020-01-18
2                   2020-01-13
2                   2020-01-16
2                   2020-01-21

and I want to get the latest date for every id. I want to get these values:

Code: Select all

id                  date
1                   2020-01-18
2                   2020-01-21

I tried to use MAX() with

Code: Select all

SELECT id, MAX(date) FROM Table ORDER BY id ASC

but it only returnes 2 | 2020-01-21. What is the right query? Any help would be appreciated.

Posted: **Thu Jan 23, 2020 8:11 pm**

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

Posted: **Fri Jan 24, 2020 7:55 am**

All I wanted was a "GROUP BY id"....
Thank you very very much!!!

PureBasic Forums - English

SQLite query to get last row for every id

SQLite query to get last row for every id

Re: SQLite query to get last row for every id

Re: SQLite query to get last row for every id