List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 7 2005 1:48am
Subject:Re: Selecting a group of distinct items?
View as plain text  
Donnie Lynch <darklists@stripped> 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
Thread
Selecting a group of distinct items?Donnie Lynch6 Oct
  • Re: Selecting a group of distinct items?Manish Marathe7 Oct
    • Re: Selecting a group of distinct items?Jasper Bryant-Greene7 Oct
      • Re: Selecting a group of distinct items?Manish Marathe7 Oct
        • Re: Selecting a group of distinct items?Jasper Bryant-Greene7 Oct
  • Re: Selecting a group of distinct items?Chris7 Oct
  • Re: Selecting a group of distinct items?SGreen7 Oct