i don't think i missed any points raised by anyone in this discussion.
email is often a difficult medium for technical issues.
for most of our purposes, we run multiple queries in order to make sure
we are actually getting the data we want. it makes the code simpler,
easier to understand and
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.
-Original Message-
what we want is the value for the name field corresponding to the row
with the most recent close_date.
Based on that comment, I'd
select name from TD order by close_date DESC limit 1
something like this:
select max(close_date), symbol, name from TD where
Ed Lazor wrote:
-Original Message-
what we want is the value for the name field corresponding to the row
with the most recent close_date.
Based on that comment, I'd
select name from TD order by close_date DESC limit 1
except, we run into problems when there is a list of values for symbol
How about
select close_date, symbol, name
from TD
where symbol in (list of
characters) limit 1
ORDER BY close_date desc
Jeff Mathis [EMAIL PROTECTED] wrote:
hello query gurus.
we have a table TD with the following columns:
close_date
symbol
name
close_date is just a date field
there
I'll be more explicit:
select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol, name order by symbol;
returns
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 |
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
+-++-+
| max(close_date) | symbol | name|
+-++-+
| 2004-10-05 | aa | cmptrhw |
| 2004-10-05 | bb | biotech |
| 2004-10-05 | cc | biotech |
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
Jeff Mathis writes:
now, leaving off name from the group by clause
(select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol;)
gives
+-++-+
| max(close_date) | symbol | name|
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
4.1.3 and the innodb engine on solaris 5.8
Brad Eacker wrote:
Jeff Mathis writes:
now, leaving off name from the group by clause
(select max(close_date), symbol, name from TD
where symbol in ('aa','bb','cc') group by symbol;)
gives
+-++-+
| max(close_date) |
well, obviously some, if not all, of what you are saying is true. your
table example below though is not the same as mine. My table stores time
series data. for every symbol, there are a series of rows all with
different dates. there is a unique constraint on the combination of
symbol and
Jeff Mathis wrote:
well, obviously some, if not all, of what you are saying is true. your
I don't believe I said anything untrue. Did you have something in mind?
table example below though is not the same as mine. My table stores time
series data. for every symbol, there are a series of rows
14 matches
Mail list logo
