Android Question SQLite Max Value in RDC

[johnB]

I have the following sql statement in my config.properties file

sql.select_stoploss=SELECT Prices2.Stock, Prices2.Date, max(Prices2.Close) FROM Prices2 WHERE Prices2.Stock = ? AND Prices2.Date >= ?

(PS I only put the Table name in because I thought that Date may have been some sort of reserved word in SQLite, I get the same results without the table name prefix))

It returns the correct Max Value for Close but returns the Date of the last record in the file for the SELECTed WHERE Stock. It returns the correct Stock code but that is in the WHERE statement.

I run this query in DB Browser

SELECT Prices2.Stock, Prices2.Date, max(Prices2.Close) FROM Prices2 WHERE Prices2.Stock = 'BHP.AX' AND Prices2.Date >= '2014-06-07'

AND I get the correct Close and Date

I was wondering if anybody had any ideas

Thanks in advance

 

[Erel]

Your query will return many rows. It will return all the rows that match the where clauses.

You can user ORDER BY Prices2.Close DESC to return the row with the max Close value as the first row.

 

[johnB]

Thanks for you response

Just to complete the post.

I had already done what you have suggested so that I could progress but prefer the idea of only getting back one record rather than many.

I think that you may have misunderstood part of my original post.

The query I posted worked in the SQLite browser I was using ie returned only one record and the correct one, so I downloaded a second browser to check that result and it also returned only 1 record with the correct date and close.

I'll keep processing the first record of the many I get back by selecting all the records picked up by the WHERE and ORDER by DESC

Thanks again

 

[keirS]

If understand you correctly this should get what you want.

SELECT Prices2.Stock, Prices2.Date, max(Prices2.Close) FROM Prices2 WHERE Prices2.Close = (select max(Prices2.Close) FROM Prices2 WHERE Prices2.Stock = 'BHP.AX' AND Prices2.Date >= '2014-06-07') AND Prices2.Stock = 'BHP.AX' AND Prices2.Date >= '2014-06-07'

 

[johnB]

Thanks Keir, Yes that worked in the SQLite Browser.

I'll try it later in config.properties but it seemed to take a long time to process in the browser. I'll see which is faster. Selecting all records for the stock by date, Order by DESC and only processing the first record or your using your code

Thanks for your time