Android Question Little help on Sql

Discussion in 'Android Questions' started by marcick, May 6, 2015.

  1. marcick

    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 ?

    Code:
    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
     
    Peter Simpson and inakigarm like this.
  2. CanguroCode

    CanguroCode Active Member Licensed User

    I use this query:

    Code:
    "SELECT MAX(id) FROM table1"
     
  3. marcick

    marcick Well-Known Member Licensed User

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

    DonManfred Expert Licensed User

    Code:
    SELECT * FROM table1 ORDER BY id DESC LIMIT 0,1;
     
  5. marcick

    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.
     
  6. DonManfred

    DonManfred Expert Licensed User

    You can select the hole record and do a subselect to get the max value too

    Like this

    Code:
    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
     
  7. edgar_ortiz

    edgar_ortiz Active Member Licensed User

    In fact that's the best way.
     
  8. sorex

    sorex Expert Licensed User

    here's another method...

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

    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.
     
  10. sorex

    sorex Expert Licensed User

    why do you need to scan the columns?

    * returns all record fields so you can just access them
     
  11. marcick

    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 ?
     
  12. sorex

    sorex Expert Licensed User

    somthing like

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

    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.
     
  14. marcick

    marcick Well-Known Member Licensed User

    ok, thanks again anyway ...
    Bye
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice