How to Use Aggregate (MAX) in Where Clause?

[ilcaa]

I have a Table, "Quotes", that is structured

qDate, qTicker, qClose
4/20/2015, goog, 540.93
4/19/2015, goog, 539.30
4/18/2015, goog, 537.10
....
3/19/2014, goog, 539.30
3/18/2014, goog, 537.10

I am trying to get the qClose of the last available Date (qDate). I am using the MAX(qDate) to find the last date but if I use it in my WHERE clause it errors, "Aggregate Functions not available in WHERE clause"

Select Quotes.qClose
From Quotes
Where ((qTicker = 'goog') AND (qDate = Max(qDate));

 

[sheetspread]

You can write a subquery instead or join the query to another aggregate query of all the groups' max values.

 

[ilcaa]

im not that versed in Queries

the subquery, getting the Max(aDate), by itself returns the correct date of 4/20/2015 but the entire query doesnt return the qClose of that Date

Select Quotes.qClose
From Quotes
Where ((qTicker = 'goog') AND 
(Select Max(qDate)
from Quotes
WHERE qTicker ='goog');

 

[ilcaa]

thanks for the advise, I finnally got it to work...

Select qCLose
From Quotes
Where qTicker ='goog' and qDate=
(select Max(qDate) from Quotes WHERE qticker='goog');

 

[sheetspread]