Android Question Little help on Sql

marcick

Well-Known Member
Licensed User
Hi,
I need to extract the most recent record of a certain Id, is this way correct or there is a more quick and elegant way using the Max function ?

B4X:
Dim Cursor1 As Cursor
    Dim query As String="SELECT * FROM records WHERE ID = John ORDER BY InsDate DESC LIMIT 1"
       Cursor1 = MySub.SQL_Bls1.ExecQuery(query)
    Cursor1.Position=0   
    Log(Cursor1.GetString("InsDate") & " - " & Cursor1.GetInt("Size"))
Thanks
 

marcick

Well-Known Member
Licensed User
This is how I do. I was asking if, instead to use cursor, was possible to retrieve an entire record using the max function.
 

DonManfred

Expert
Licensed User
I was asking if, instead to use cursor, was possible to retrieve an entire record using the max function.
You can select the hole record and do a subselect to get the max value too

Like this

B4X:
select *,(select max(id) from table) as maxid
from table
where ...
ORDER BY field ASC
LIMIT 0,x
But i DONT know whether it works with sqlite like in mysql too
give it a try
 

edgar_ortiz

Active Member
Licensed User
Hi,
I need to extract the most recent record of a certain Id, is this way correct or there is a more quick and elegant way using the Max function ?

B4X:
Dim Cursor1 As Cursor
    Dim query As String="SELECT * FROM records WHERE ID = John ORDER BY InsDate DESC LIMIT 1"
       Cursor1 = MySub.SQL_Bls1.ExecQuery(query)
    Cursor1.Position=0  
    Log(Cursor1.GetString("InsDate") & " - " & Cursor1.GetInt("Size"))
Thanks
In fact that's the best way.
 

sorex

Expert
Licensed User
here's another method...

B4X:
SELECT * FROM records WHERE ID = (select max(ID) from records)
no sorting involved so it might be faster.
 

marcick

Well-Known Member
Licensed User
Thanks everybody, but anyway we are talking of using then the cursor to extract the columns.
The result of the query is just one record, so I have to set the cursor to 0 and scan the columns.
I was asking myself if, with ExecQuerySingleResult instead of ExecQuery, was possible to extract a whole record and not a single field.
 

sorex

Expert
Licensed User
why do you need to scan the columns?

* returns all record fields so you can just access them
 

marcick

Well-Known Member
Licensed User
look to my first post. I need to extract the most recent record of a certain Id, then scan the various column.
I did it with a cursor, how would you do in a single line ?
 

sorex

Expert
Licensed User
somthing like

B4X:
records=SQL.querySQLite("SELECT * FROM records WHERE ID = (select max(ID) from records)")
records.Position=0
log(records.GetString("ID"))
 

sorex

Expert
Licensed User
ok, I misinterpret your initial post, your query was already good. I thought you wanted the last ID as single recordset.
 
Top