Trouble with Max function

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

HI All

I am trying to get the last or latest stock price from table of daily
stock prices of companies i keep track of.

I have a daily-stock-price table and when i do the query using the max
function i get far to many records

I am have about 190 or so companies, however this code returns about 56,
000

If i do the query without the close price (Close) it returns the last
date as expected for each comapny, but when i add the close field i get
to many. not sure what it is doing

SELECT [Daily-Stock-Prices].Code, Max([Daily-Stock-Prices].Date) AS
MaxOfDate, [Daily-Stock-Prices].Close
FROM [Daily-Stock-Prices]
GROUP BY [Daily-Stock-Prices].Code, [Daily-Stock-Prices].Close
having max([Daily-Stock-Prices].date );


Hope someone can help

Greg
 
Greg said:
HI All

I am trying to get the last or latest stock price from table of daily
stock prices of companies i keep track of.

I have a daily-stock-price table and when i do the query using the max
function i get far to many records

I am have about 190 or so companies, however this code returns about
56, 000

If i do the query without the close price (Close) it returns the last
date as expected for each comapny, but when i add the close field i
get to many. not sure what it is doing

SELECT [Daily-Stock-Prices].Code, Max([Daily-Stock-Prices].Date) AS
MaxOfDate, [Daily-Stock-Prices].Close
FROM [Daily-Stock-Prices]
GROUP BY [Daily-Stock-Prices].Code, [Daily-Stock-Prices].Close
having max([Daily-Stock-Prices].date );
Click to expand...

Greg -

Were you unable to adapt the query I gave you earlier for the month-end
price? Ot did that not work for you? This is a similar situation,
though simpler. You're going to need a subquery.
 
"Date" and "Close" are Reserved Words in Access and should not be use
in the naming of objects in your application.
Using Reserved Words can cause unexplained errors and strange behavio
in an application.

I recommend that you change these names to something different.

RD
 
HI dirk
yes i did get the query working (i had reposted) and i have realised the
same thing was required for this question,

i managed to sort it out so sorry for inconveniance, what i would like
to try and understand is what access is doing in these sorts of
situations it would be a reasonable assumption that you select the
latest date for each record and it should return the latest price,

I am in the middle of exams at present and havent a lot of time to nut
this one out properly, but i take on board about my naming conventions
and avoid these words

As a question would this be quicker in recordsets with ADO?, or even
possible.

all the best

Greg

Dirk said:
HI All

I am trying to get the last or latest stock price from table of daily
stock prices of companies i keep track of.

I have a daily-stock-price table and when i do the query using the max
function i get far to many records

I am have about 190 or so companies, however this code returns about
56, 000

If i do the query without the close price (Close) it returns the last
date as expected for each comapny, but when i add the close field i
get to many. not sure what it is doing

SELECT [Daily-Stock-Prices].Code, Max([Daily-Stock-Prices].Date) AS
MaxOfDate, [Daily-Stock-Prices].Close
FROM [Daily-Stock-Prices]
GROUP BY [Daily-Stock-Prices].Code, [Daily-Stock-Prices].Close
having max([Daily-Stock-Prices].date );
Click to expand...


Greg -

Were you unable to adapt the query I gave you earlier for the month-end
price? Ot did that not work for you? This is a similar situation,
though simpler. You're going to need a subquery.
Click to expand...
 
Greg said:
HI dirk
yes i did get the query working (i had reposted) and i have realised
the same thing was required for this question,

i managed to sort it out so sorry for inconveniance, what i would like
to try and understand is what access is doing in these sorts of
situations it would be a reasonable assumption that you select the
latest date for each record and it should return the latest price,
Click to expand...

Well, that's true, but you have to take into account the way that SQL
works. In a totals query, i.e. one that uses the GROUP BY clause, SQL
allows you only to select two types of fields: those you are grouping
by, and those that are the result of aggregate expressions (like Sum(),
Max(), Min(), etc.) that operate at the group level. You want to
extract data from the field [Close], so that means that in a *single*
SELECT statement you have to either group by that field or return, say,
Max([Close]). But if you group by
Code: 
 and [Close], you're going to
get a separate record back for each combination of [Code] and [Close],
and that's not what you want, while if you take Max([Close]) for each
[Code] it isn't necessarily (or even likely) the case that the highest
value of [Close] for each [Code] is the latest one.

So you need a subquery of some sort to identify the latest date for each
[Code], and then you can use that in the main query to filter the
results.  There are several ways to go about this, as you saw if you
read that web page I pointed you to last time.  One would be to embody
the subquery as what I think they call a "derived table", and then join
on it.  That's the approach I suggested last time.  If we follow that
same approach this time, we can first define a SELECT statement that
gets the latest date for each [Code], like this:

SELECT
[Code],
Max([Date]) As LastDate
FROM
[Daily-Stock-Prices]
GROUP BY
[Code]

That will give us one record per [Code], containing the [Code] and the
latest date in the table for that [Code].  Then, since as I understand
it there's only one record per [Code] per day, we can join the results
of that SELECT statement back to the table to get the records we want:

SELECT
P.Code,
L.LastDate,
P.Close
FROM
[Daily-Stock-Prices] As P
INNER JOIN
(SELECT
[Code],
Max([Date]) As LastDate
FROM
[Daily-Stock-Prices]
GROUP BY
[Code]
) As L
ON P.Code = L.Code;

That's "air SQL" but something like that ought to work.
[QUOTE]
I am in the middle of exams at present and havent a lot of time to nut
this one out properly, but i take on board about my naming conventions
and avoid these words[/QUOTE]

That's will make your life easier in the future.  Good luck
[QUOTE]
As a question would this be quicker in recordsets with ADO?, or even
possible.[/QUOTE]

It would be possible, using a couple of recordsets based on the
different SELECT statements, but it would be painfully slow.  Pretty
much anything that can be done using straight SQL is *much* faster done
that way than using recordsets.  You tell the query engine *what* you
want, and it figures out the best way to give it to you.
 
HI Dirk
many thanks for the time and effort you took to explain this area, i now
have a better idea of what is happening. once you know that it makes
life a bit simpler

you can read as many books as you like but it is untill you get your
hands dirty that you really start to learn.

this little project of mine may not be going anywhere but it is
certainly a good way of learning to manage a lot of data

I was aware of not using reserved words howeverthat was for VB and VBA
etc didnt think applied to access nor was a aware

any way thanks Dirk i will print off this little lesson and file it for
future head banging nights :)

All the best from OZ

Greg


Dirk said:
HI dirk
yes i did get the query working (i had reposted) and i have realised
the same thing was required for this question,

i managed to sort it out so sorry for inconveniance, what i would like
to try and understand is what access is doing in these sorts of
situations it would be a reasonable assumption that you select the
latest date for each record and it should return the latest price,
Click to expand...


Well, that's true, but you have to take into account the way that SQL
works. In a totals query, i.e. one that uses the GROUP BY clause, SQL
allows you only to select two types of fields: those you are grouping
by, and those that are the result of aggregate expressions (like Sum(),
Max(), Min(), etc.) that operate at the group level. You want to
extract data from the field [Close], so that means that in a *single*
SELECT statement you have to either group by that field or return, say,
Max([Close]). But if you group by
Code: 
 and [Close], you're going to
get a separate record back for each combination of [Code] and [Close],
and that's not what you want, while if you take Max([Close]) for each
[Code] it isn't necessarily (or even likely) the case that the highest
value of [Close] for each [Code] is the latest one.

So you need a subquery of some sort to identify the latest date for each
[Code], and then you can use that in the main query to filter the
results.  There are several ways to go about this, as you saw if you
read that web page I pointed you to last time.  One would be to embody
the subquery as what I think they call a "derived table", and then join
on it.  That's the approach I suggested last time.  If we follow that
same approach this time, we can first define a SELECT statement that
gets the latest date for each [Code], like this:

SELECT
[Code],
Max([Date]) As LastDate
FROM
[Daily-Stock-Prices]
GROUP BY
[Code]

That will give us one record per [Code], containing the [Code] and the
latest date in the table for that [Code].  Then, since as I understand
it there's only one record per [Code] per day, we can join the results
of that SELECT statement back to the table to get the records we want:

SELECT
P.Code,
L.LastDate,
P.Close
FROM
[Daily-Stock-Prices] As P
INNER JOIN
(SELECT
[Code],
Max([Date]) As LastDate
FROM
[Daily-Stock-Prices]
GROUP BY
[Code]
) As L
ON P.Code = L.Code;

That's "air SQL" but something like that ought to work.

[QUOTE]
I am in the middle of exams at present and havent a lot of time to nut
this one out properly, but i take on board about my naming conventions
and avoid these words[/QUOTE]


That's will make your life easier in the future.  Good luck

[QUOTE]
As a question would this be quicker in recordsets with ADO?, or even
possible.[/QUOTE]


It would be possible, using a couple of recordsets based on the
different SELECT statements, but it would be painfully slow.  Pretty
much anything that can be done using straight SQL is *much* faster done
that way than using recordsets.  You tell the query engine *what* you
want, and it figures out the best way to give it to you.
[/QUOTE]
Click to expand...
 
Back
Top