we really don't want to issue two queries. this should be able to be
done in one, and without using temp tables, but maybe not.
thanks for the help though
jeff
[EMAIL PROTECTED] wrote:
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
X-Mozilla-Status: 8000
X-Mozilla-Status2: 00000000
WHERE symbol in (<list of symbols>)
GROUP BY symbol;
SELECT ts.symbol, ts.last_date, TD.name
FROM tmpSymbols ts
X-Mozilla-Status: 8000
X-Mozilla-Status2: 00000000
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]
--
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]
