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
FROM TD
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;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Jeff Mathis <[EMAIL PROTECTED]> wrote on 10/14/2004 02:22:32 PM:
> hello query gurus.
>
> we have a table TD with the following columns:
>
> close_date
> symbol
> name
>
> close_date is just a date field
>
> there is a unique constraint on the combination of close_date and
symbol.
>
> 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?
>
> jeff
>
> ps we're using mysql 4.1.3 with the innodb engine
>
>
> --
> 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]
>
