Donnie Lynch <[EMAIL PROTECTED]> wrote on 10/06/2005 05:33:01 PM: > 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 >
Actually, this is THE most frequently asked technique on the list. The problem is, if you are new, you can't see the pattern and don't know how to recognize that this is what you are doing. What you are trying to find is known as the group-wize maximum and is well documented here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html That and several other common query patterns are in this section of the manual: http://dev.mysql.com/doc/mysql/en/examples.html HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
