SQLlite How to get MAX value from some field

[Jaames]

Hello..

I have one question, can you please help me?

i have this function which returned value is always zero, why?
can you catch problem in here?

Sub ReturnMaxFromTable(TableName As String, FieldName As String) As Int

Dim TempSQL As SQL
Dim TempCur As Cursor

TempSQL.Initialize(File.DirDefaultExternal,"mydb.db",False)

TempCur = TempSQL.ExecQuery("SELECT MAX ('" & FieldName & "') AS 'MaxValue' FROM '" & TableName & "' ")'I'm querying table which is filled with Int values

Log("TempCur.RowCount = " & TempCur.RowCount) ' This is always 1
If TempCur.RowCount > 0 Then
   TempCur.Position = 0
   Log("TempCur.GetInt(''MaxValue'') = " & TempCur.GetInt("MaxValue")) ' but this is always 0 (zero)
   Return TempCur.GetInt("MaxValue")
Else
   Return 0
End If

TempCur.Close
TempSQL.Close

End Sub

Any help would be appreciated
Thank You.

 

[DouglasNYoung]

Jaames,
Change the SQL string to

TempCur = TempSQL.ExecQuery("SELECT MAX(" & FieldName & ") 'MaxValue' FROM " & TableName & ")"

i.e you don't need the single quotes round the field names!

Douglas

 

[Kiffi]

this query should do the job:

TempCur = TempSQL.ExecQuery("SELECT MAX (" & FieldName & ") AS MaxValue FROM " & TableName)

moreover this code is a little bit cleaner (closes always
the cursor and database):

Sub ReturnMaxFromTable(TableName As String, FieldName As String) As Int

   Dim TempSQL As SQL
   Dim TempCur As Cursor
   Dim ReturnValue As Int

   TempSQL.Initialize(File.DirInternal, "database.db", False)

   TempCur = TempSQL.ExecQuery("SELECT MAX (" & FieldName & ") AS MaxValue FROM " & TableName)

   Log("TempCur.RowCount = " & TempCur.RowCount) ' This is always 1
   
   If TempCur.RowCount > 0 Then
      TempCur.Position = 0
      ReturnValue = TempCur.GetInt("MaxValue")
      Log("TempCur.GetInt(''MaxValue'') = " & ReturnValue) 
   Else
      ReturnValue = 0
   End If

   TempCur.Close
   TempSQL.Close

   Return ReturnValue

End Sub

and here is the short version:

Sub ReturnMaxFromTable(TableName As String, FieldName As String) As Int

   Dim TempSQL As SQL
   Dim ReturnValue As Int

   TempSQL.Initialize(File.DirInternal, "database.db", False)

   ReturnValue = TempSQL.ExecQuerySingleResult("SELECT MAX (" & FieldName & ") AS MaxValue FROM " & TableName)

   TempSQL.Close

   Return ReturnValue

End Sub

Greetings ... Kiffi

 

[klaus]

Try this code:

Max = TempSQL.ExecuteQuerySingleResult("SELECT MAX (" & FieldName & ") FROM " & TableName)

Best regards

 

[Jaames]

Problem Solved

This solved my problem :

TempCur = TempSQL.ExecQuery("SELECT MAX (" & FieldName & ") AS MaxValue FROM " & TableName)

But I did not notice .ExecQuerySingleResult function :sign0144:

so i used Kiffi's code which is the best solution in this case i think :

Sub ReturnMaxFromTable(TableName As String, FieldName As String) As Int

    Dim TempSQL As SQL
    Dim ReturnValue As Int

    TempSQL.Initialize(File.DirInternal, "database.db", False)

    ReturnValue = TempSQL.ExecQuerySingleResult("SELECT MAX (" & FieldName & ") AS MaxValue FROM " & TableName)

    TempSQL.Close

    Return ReturnValue

End Sub

Thank you for such a quick response and kindness.

 

[JTmartins]

Error

I was just testing the situations above, and they trow me an error if the table is empty. Null Pointer Exception.

I actually need one of this things, for sync purposes with a remote DB, and lots of times the local DB can be empty, depending on what the device user has done.

so if it is empty I would need to get a zero or a null.

My solution was

Sub DevolveMaxdaTabela(TableName As String, FieldName As String) As Int

    Dim TempSQL As SQL
    Dim ReturnValue As Int
   Dim tempvalue As Int
    TempSQL.Initialize(File.DirDefaultExternal, Main.BaseDados, False)
   tempvalue=TempSQL.ExecQuerySingleResult ("SELECT COUNT(*) FROM " & TableName)
   If tempvalue=0 Then
    ReturnValue=0
     
       else

   
        'ReturnValue = TempSQL.ExecQuerySingleResult("SELECT MAX (" & FieldName & ") AS MaxValue FROM " & TableName)
    ReturnValue = TempSQL.ExecQuerySingleResult("SELECT MAX (" & FieldName & ") FROM " & TableName)
    
end if

     TempSQL.Close
    Return ReturnValue

End Sub

So before actually select the MAX, I count the number of records to ensure there is someting in the table and only after that step I proceed to looking for the MAXvalue.