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 (<list 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]
