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

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