Android Question RDC and SQLite

[johnB]

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

 

[Erel]

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

 

[johnB]

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

 

[Erel]

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)?

 

[johnB]

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

 

[johnB]

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