It takes two steps: first determine the max(closedate) for each symbol,
then use those results to get the name field. You could do this with a
subquery (both steps in the one statement) because you are using a version
of MySQL > 4.0.0 but here is a temp table implementation that will work
with just about anyone.
CREATE TEMPORARY TABLE tmpSymbols
SELECT symbol, max(close_date) as last_date
WHERE symbol in (<list of symbols>)
GROUP BY symbol;
SELECT ts.symbol, ts.last_date, TD.name
FROM tmpSymbols ts
INNER JOIN TD
ON TD.symbol = ts.symbol
AND TD.close_date = ts.last_date;
Unimin Corporation - Spruce Pine
Jeff Mathis <jmathis@stripped> wrote on 10/14/2004 02:22:32 PM:
> hello query gurus.
> we have a table TD with the following columns:
> close_date is just a date field
> there is a unique constraint on the combination of close_date and
> what we want is the value for the name field corresponding to the row
> with the most recent close_date.
> 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?
> ps we're using mysql 4.1.3 with the innodb engine
> Jeff Mathis, Ph.D. 505-955-1434
> Prediction Company jmathis@stripped
> 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/mysql?unsub=1