your example using the subsquery works. when we used the subquery approach, we forgot to include the equivalent of t1.symbol = t2.symbol.
if we use:
select max(close_date), symbol, name from TD where symbol in (<quoted char string>) group by symbol, name order by symbol;
we end up getting multiple rows for each symbol if the names change over time. but thats ok for now -- we can parse the query output within our application and get the one row with the most recent date.
what we want to get is conceptually simple, but perhaps not so in terms of SQL.
jeff
Michael Stassen wrote:
No, Shawn's answer is correct. You are starting from a false assumption. You are expecting that MAX(closedate) corresponds to a row. It does not. Consider the table
Table=stuff: sym val note --- --- ---- AAA 2 one AAA 2 two AAA 4 three AAA 6 four AAA 12 five AAA 7 six AAA 12 seven BBB 1 eight BBB 2 nine BBB 3 ten
Now consider the query
SELECT sym, note, MAX(val), MIN(val), AVG(val) FROM stuff GROUP BY sym;
Which row should be returned for sym='AAA'? Do you see the problem? It is clear that, for sym=AAA, MIN(val) is 2, MAX(val) is 12, and AVG(val) is about 6.43. Which row is that? The answer is that it's not a row. MAX(), MIN(), and AVG() are aggregate functions. They do not return rows, they return summary stats about a set of rows.
Many dbs wouldn't even allow a query like that, because we are asking for a column not included in the GROUP BY. Mysql allows that, but the manual warns that it is pointless to do so if the extra column does not have a unique value per group <http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html>.
Finding the notes which correspond to the maximum val is fundamentally a 2 step process. First you must find the maximum val, then you must find the rows(s) which have that val. This is what Shawn was telling you.
The manual suggests 3 ways to solve this problem <http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html>. The most efficient solution, and the one that works in all versions of mysql, is to use a temporary table, as Shawn described. As you have mysql 4.1, you could accomplish the same thing with a subquery. In your case, that would be
SELECT close_date, symbol, name FROM TD t1 WHERE close_date = (SELECT MAX(t2.close_date) FROM TD t2 WHERE t1.symbol = t2.symbol) AND symbol IN (<list of characters>);
Note that this is still really a 2 step process. The subquery handles the first step, finding the max close_date, while the parent query handles step 2, finding the matching rows.
There is a third way, the MAX-CONCAT trick. It does it in one query without subqueries, and is very inefficient. See the manual for the details.
In other words, this wasn't such a simple query, after all.
Michael
Jeff Mathis wrote:
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 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
-- 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]
