Something like this ought to work (untested). If you're using < 4.1 you
will need a temporary table as a sub-query will not work.
SELECT
name,
date,
value
FROM yourtable
WHERE date = (
SELECT
MAX(date)
FROM yourtable yourtable2
WHERE yourtable.name = yourtable2.name
)
Off the top of my head that seems the way to go, if anyone else cna do
it better please speak up.
Chris
Donnie Lynch wrote:
Hi,
I'm pretty much an SQL newbie, so apologies in advance if this is
basic stuff. That'll teach me for skipping the databases class in
college...
I just created an app using a table that has entries like this
simplified version:
name date value
----------------------------
Fred 2005-10-01 7
Fred 2005-10-02 10
Joe 2005-10-01 4
Joe 2005-10-01 10
and so on. New values get inserted every day, but old ones remain.
I'd like a query that selects the "value" field for the most recent
date for each name. Since I didn't know how, right now I'm selecting
all the distinct names and looping over those in PHP to do queries
that grab the latest value from each. But next I want to be able to
sort that overall list by value, which just leads to more and more
complexity with my workaround.
If all else fails, I suppose I can have that loop create a temporary
table with each name and then sort that, but it seems like there has
to be a cleaner solution out there. Is there?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
