List:General Discussion« Previous MessageNext Message »
From:Manish Marathe Date:October 6 2005 11:17pm
Subject:Re: Selecting a group of distinct items?
View as plain text  
SELECT DISTINCT(name), date, value FROM table_name ORDER BY date DESC;

On Thu, 2005-10-06 at 17:33 -0400, 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
> 

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