Better way

  • Thread starter Thread starter Maxie
  • Start date Start date
M

Maxie

I have a query problem. Below is a query that works.
However, since I'm trying to do such a simple thing, I
can't help but think that there must be a much easier way
to do what I'm trying to do here.

SELECT ticker, date, close
FROM pub_price_history
WHERE date in (SELECT DISTINCT TOP 1 date from
pub_price_history ORDER BY date DESC);

All I want to do is for each ticker in the table, to get
the record with the latest closing price.

In case, it helps: pub_price_history is a table with three
fields: the date, the ticker symbol and the day's closing
price.

Is there a simpler and easier way?

Thanks,

Maxie III
 
Try this:

SELECT ticker, Max([date]) , close
FROM pub_price_history
GROUP BY ticker, Max([date]) , close;

Also, not good to use date as a field name, because Date is the name of a
VBA function in ACCESS, and ACCESS can get confused. There also are many
other words (Name, Close, etc.) that have a similar "reserved" nature.
 
I took your advice and renamed the "date" field to
"thedate". So I tried...
SELECT ticker, max(thedate) , close
FROM pub_price_history
GROUP BY ticker, max(thedate) , close;

I got the following error: "Cannot have aggregate function
in GROUP BY clause (max(thedate)).
-----Original Message-----
Try this:

SELECT ticker, Max([date]) , close
FROM pub_price_history
GROUP BY ticker, Max([date]) , close;

Also, not good to use date as a field name, because Date is the name of a
VBA function in ACCESS, and ACCESS can get confused. There also are many
other words (Name, Close, etc.) that have a similar "reserved" nature.
--
Ken Snell
<MS ACCESS MVP>


Maxie said:
I have a query problem. Below is a query that works.
However, since I'm trying to do such a simple thing, I
can't help but think that there must be a much easier way
to do what I'm trying to do here.

SELECT ticker, date, close
FROM pub_price_history
WHERE date in (SELECT DISTINCT TOP 1 date from
pub_price_history ORDER BY date DESC);

All I want to do is for each ticker in the table, to get
the record with the latest closing price.

In case, it helps: pub_price_history is a table with three
fields: the date, the ticker symbol and the day's closing
price.

Is there a simpler and easier way?

Thanks,

Maxie III
Click to expand...


.
Click to expand...
 
Maxie said:
So I tried...
SELECT ticker, max(thedate) , close
FROM pub_price_history
GROUP BY ticker, max(thedate) , close;

I got the following error: "Cannot have aggregate function
in GROUP BY clause (max(thedate)).
Click to expand...
Hi Maxie,

If your date field includes a "time portion,"
(so for one ticker there is only one max date..
so only one close value for the max date),
then I believe this will work for you:

SELECT ticker, thedate, close
FROM pub_price_history
INNER JOIN
(SELECT ticker, Max(thedate) AS MaxDate
FROM pub_price_history
GROUP BY ticker) AS qryMaxDate
ON
(pub_price_history.ticker = qryMaxDate.ticker)
AND
(pub_price_history.thedate = qryMaxDate.MaxDate);

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Maxie said:
So I tried...
SELECT ticker, max(thedate) , close
FROM pub_price_history
GROUP BY ticker, max(thedate) , close;

I got the following error: "Cannot have aggregate function
in GROUP BY clause (max(thedate)).
Click to expand...
Hi Maxie,

I believe this will work for you:

SELECT ticker, thedate, close
FROM pub_price_history
INNER JOIN
(SELECT ticker, Max(thedate) AS MaxDate
FROM pub_price_history
GROUP BY ticker) AS qryMaxDate
ON
(pub_price_history.ticker = qryMaxDate.ticker)
AND
(pub_price_history.thedate = qryMaxDate.MaxDate);

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
There should only be 1 record for each ticker/day
combo....but if something will work even if there are
multiple records for each ticker/day, this would be an
added bonus.

Also, my initial goal was to find something simpler and
more elegant than the query I already had. Here is my
original query...

SELECT ticker, thedate, close
FROM pub_price_history
WHERE thedate in (SELECT DISTINCT TOP 1 thedate from
pub_price_history ORDER BY thedate DESC);
 
Sorry....my fingers got ahead of my brain...

Use this:

SELECT ticker, Max([thedate]) , close
FROM pub_price_history
GROUP BY ticker, close;

--
Ken Snell
<MS ACCESS MVP>

Maxie said:
I took your advice and renamed the "date" field to
"thedate". So I tried...
SELECT ticker, max(thedate) , close
FROM pub_price_history
GROUP BY ticker, max(thedate) , close;

I got the following error: "Cannot have aggregate function
in GROUP BY clause (max(thedate)).
-----Original Message-----
Try this:

SELECT ticker, Max([date]) , close
FROM pub_price_history
GROUP BY ticker, Max([date]) , close;

Also, not good to use date as a field name, because Date is the name of a
VBA function in ACCESS, and ACCESS can get confused. There also are many
other words (Name, Close, etc.) that have a similar "reserved" nature.
--
Ken Snell
<MS ACCESS MVP>


Maxie said:
I have a query problem. Below is a query that works.
However, since I'm trying to do such a simple thing, I
can't help but think that there must be a much easier way
to do what I'm trying to do here.

SELECT ticker, date, close
FROM pub_price_history
WHERE date in (SELECT DISTINCT TOP 1 date from
pub_price_history ORDER BY date DESC);

All I want to do is for each ticker in the table, to get
the record with the latest closing price.

In case, it helps: pub_price_history is a table with three
fields: the date, the ticker symbol and the day's closing
price.

Is there a simpler and easier way?

Thanks,

Maxie III
Click to expand...


.
Click to expand...
Click to expand...
 
Sorry Maxie,

When I saw "ticker"
I thought data like

ticker thedate close
NY 9/10/03 some number
NK 9/11/03 some number
LON 9/11/03 some number

where at any one time you run
the query, not all "tickers"
may have a close on the same
date.

or ...say power goes out in NY
and all other exchanges have a
close for one date, but not NY
....etc.

so thought you would need
max date for each ticker, then
the close on that individual
ticker's max date.

sorry
 
So then, there's nothing better that what I had?

-----Original Message-----
Sorry Maxie,

When I saw "ticker"
I thought data like

ticker thedate close
NY 9/10/03 some number
NK 9/11/03 some number
LON 9/11/03 some number

where at any one time you run
the query, not all "tickers"
may have a close on the same
date.

or ...say power goes out in NY
and all other exchanges have a
close for one date, but not NY
....etc.

so thought you would need
max date for each ticker, then
the close on that individual
ticker's max date.

sorry




.
Click to expand...
 
This would be equivalent to what you have:
SELECT pub_price_history.*
FROM pub_price_history
WHERE
(((pub_price_history.thedate)=DMax("[thedate]","[pub_price_history]")));

If on some days, a ticker does not trade, that ticker would not show up.
To get all tickers on the last day they traded, use:
SELECT pub_price_history.*
FROM pub_price_history
WHERE
(((pub_price_history.thedate)=DMax("[thedate]","[pub_price_history]","[ticke
r]=""" & [ticker] & """")));
 
Back
Top