List:General Discussion« Previous MessageNext Message »
From:Donnie Lynch Date:October 6 2005 9:33pm
Subject:Selecting a group of distinct items?
View as plain text  
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