Android Question Little help on Sql

marcick · May 6, 2015

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

CanguroCode · May 6, 2015

I use this query:

B4X:

"SELECT MAX(id) FROM table1"

marcick · May 6, 2015

CanguroCode said:
I use this query:

B4X:

"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 · May 6, 2015

B4X:

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

marcick · May 6, 2015

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 · May 6, 2015

marcick said:
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 · May 7, 2015

marcick said:
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 · May 7, 2015

here's another method...

B4X:

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

no sorting involved so it might be faster.

marcick · May 7, 2015

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 · May 7, 2015

why do you need to scan the columns?

* returns all record fields so you can just access them

marcick · May 7, 2015

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 · May 7, 2015

somthing like

B4X:

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

sorex · May 7, 2015

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

marcick · May 7, 2015

ok, thanks again anyway ...
Bye

Android Question Little help on Sql

marcick

Well-Known Member

CanguroCode

Active Member

marcick

Well-Known Member

DonManfred

Expert

marcick

Well-Known Member

DonManfred

Expert

edgar_ortiz

Active Member

sorex

Expert

marcick

Well-Known Member

sorex

Expert

marcick

Well-Known Member

sorex

Expert

sorex

Expert

marcick

Well-Known Member

Similar Threads

Android Question Little help on Sql

Well-Known Member

Active Member

Well-Known Member

Expert

Well-Known Member

Expert

Active Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency