Android Question RDC and SQLite

Discussion in 'Android Questions' started by johnB, Jun 16, 2015.

  1. johnB

    johnB Active Member Licensed User

    Hi
    I hope somebody can point me in the right direction

    I have a query

    "SELECT Stock, TranDate, MAX(Close) FROM Prices1 WHERE Stock = 'XXX.AX' AND TranDate >= '2015-01-01'"
    which produced the result I would expect in two SQL Browsers running on my PC ie giving the Stock code that I inquired on, the MAX(Close) price and the Date of the MAX(Close) record

    When I run this command in config.properties

    "sql.select_stoploss_01=SELECT Stock, TranDate, MAX(Close) FROM Prices1 WHERE Stock = ? AND TranDate >= ?"

    I get the correct Stock Code and MAX(Close) value but the TranDate is the Date of the lastest (last) record in the table for the Stock Code.

    The Select query gets run for numerous Codes and the results are all the same, correct code and MAX(Close) but incorrect TranDate

    Thanks in Anticipation
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    I don't see how it can happen. Does it return the correct result if you remove MAX(close) ?
     
  3. johnB

    johnB Active Member Licensed User

    Erel, thanks for your prompt reply, I'm not sure I understand your instruction.

    In config.properties -
    If I remove MAX(Close), I'll get many records after the TranDate without the Close value that I'm after and
    if I just remove MAX and leave the field Close, I'll get all records since the TranDate which could be hundreds and I'll need to loop thru them all to find the one with the highest Close value
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    Your query will return all the results that match the WHERE clauses. The MAX(close) value will be duplicates to all results.

    Do you want to get only the record with the maximum close value (and that matches the WHERE clauses)?
     
  5. johnB

    johnB Active Member Licensed User

    Yes, that's right, I just want the MAX close value of the records that matches the WHERE clauses. So i'm expecting one record returned with the Stock code. the Date of the record with the MAX (highest) Close price and the Max Close price

    My statement
    "sql.select_stoploss_01=SELECT Stock, TranDate, MAX(Close) FROM Prices1 WHERE Stock = ? AND TranDate >= ?" is returning only one record, it's just that the Date is that of the last (newest) record in the SELECT statement rather than the record with the MAX Close. The MAX close value is correct
     
  6. johnB

    johnB Active Member Licensed User

    This got the result

    "sql.select_stoploss_01=SELECT Stock, TranDate, Close FROM Prices1 WHERE Stock = ? AND TranDate >= ? AND Close = (SELECT MAX(Close) FROM Prices1 WHERE Stock = ? AND TranDate >= ?)"

    Thanks for the help, the confusing part was that 2 SQL Browsers were giving the right result with the short but apparently syntactically incorrect SQL statement
     
    Erel likes this.
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