List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 14 2004 6:56pm
Subject:Re: simple but frustrating query
View as plain text  
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 <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
> 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         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
> 

Thread
simple but frustrating queryJeff Mathis14 Oct
  • RE: simple but frustrating queryEd Lazor14 Oct
  • Re: simple but frustrating queryJoe Audette14 Oct
  • Re: simple but frustrating querySGreen14 Oct
    • Re: simple but frustrating queryJeff Mathis14 Oct
      • Re: simple but frustrating queryMichael Stassen14 Oct
        • Re: simple but frustrating queryJeff Mathis15 Oct
          • Re: simple but frustrating queryMichael Stassen15 Oct
            • Re: simple but frustrating queryJeff Mathis15 Oct
Re: simple but frustrating queryJeff Mathis14 Oct
  • Re: simple but frustrating queryJeff Mathis14 Oct
    • Re: simple but frustrating queryJoe Audette14 Oct
Re: simple but frustrating queryBrad Eacker14 Oct
  • Re: simple but frustrating queryJeff Mathis15 Oct