Better way

[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

 

[Ken Snell]

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.

 

[Maxie]

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>

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

.

 

[Gary Walter]

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

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

 

[Gary Walter]

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

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

 

[Maxie]

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

 

[Ken Snell]

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>

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>

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

.

 

[Gary Walter]

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

 

[Maxie]

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




.

 

[John Verhagen]

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] & """")));