>
+-----------------+--------+-------------+
| max(close_date) | symbol | name |
+-----------------+--------+-------------+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
+-----------------+--------+-------------+
.
OK I see what you want, this should do it
select close_date, symbol, name from TD
where symbol in ('aa','bb','cc') AND close_date = (SELECT max(close_date) FROM TD)
Jeff Mathis <[EMAIL PROTECTED]> wrote:
I'll be more explicit:
select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol, name order by symbol;
returns
+-----------------+--------+-------------+
| max(close_date) | symbol | name |
+-----------------+--------+-------------+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2002-05-03 | bb | drugs |
| 2002-02-05 | bb | medprovr |
| 2004-10-05 | cc | biotech |
| 2002-05-03 | cc | drugs |
| 2002-02-05 | cc | infosvcs |
+-----------------+--------+-------------+
now, leaving off name from the group by clause
(select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol;)
gives
+-----------------+--------+-------------+
| max(close_date) | symbol | name |
+-----------------+--------+-------------+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | drugs |
| 2004-10-05 | cc | infosvcs |
+-----------------+--------+-------------+
which is wrong. what we want is
+-----------------+--------+-------------+
| max(close_date) | symbol | name |
+-----------------+--------+-------------+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
+-----------------+--------+-------------+
but we can't seem to fomrulate the query.
Jeff Mathis wrote:
> Ed Lazor wrote:
>
>>> -----Original Message-----
>>> what we want is the value for the name field corresponding to the row
>>> with the most recent close_date.
>>
>>
>>
>> Based on that comment, I'd
>>
>> select name from TD order by close_date DESC limit 1
>
>
> except, we run into problems when there is a list of values for symbol
> in the query.
>
> for example
>
> select max(close_date), symbol, name from TD where symbol in
> ('aa','bb','cc','dd','ee') ....
>
> in fact this is the real problem. for a single value of symbol, we can
> do this query. but we want to feed in a list of values for symbol
>
>
>
>>
>>
>>
>>> something like this:
>>>
>>> select max(close_date), symbol, name from TD where symbol in (
>>> of characters>) group by symbol;
>>>
>>> this returns the max close_date value, but the name that is returned
>>> is garbage and seems to point to the earliest row in the table.
>>>
>>> any suggestions?
>>>
>>> jeff
>>
>>
>>
>
>
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6 http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
