Android Question Little help on Sql

[marcick]

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 ?

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

 

[CanguroCode]

I use this query:

"SELECT MAX(id) FROM table1"

 

[marcick]

I use this query:

"SELECT MAX(id) FROM table1"

Yes, but this way I just get the max value, how to get the entire record that has the max value of that column ?

 

[DonManfred]

SELECT * FROM table1 ORDER BY id DESC LIMIT 0,1;

 

[marcick]

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]

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

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]

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 ?

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]

here's another method...

SELECT * FROM records WHERE ID = (select max(ID) from records)

no sorting involved so it might be faster.

 

[marcick]

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]

why do you need to scan the columns?

* returns all record fields so you can just access them

 

[marcick]

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]

somthing like

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

 

[sorex]

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

 

[marcick]

ok, thanks again anyway ...
Bye