How to get latest date for each distinct entry?

Hi,

I have a table called "obs" with the following:

observation INT AUTO_INCREMENT,
morph VARCHAR,
date DATE,
plot VARCHAR,
number INT,
comment TEXT,
week VARCHAR

observation is the primary key, and morph is a foreign key.

How do I make a query such that I get all the latest observation
'dates' of every distinct 'morph'?

On 18 Jan, 14:55, Plantky <ngokang...@gmail.com> wrote:
> Hi,
>
> I have a table called "obs" with the following:
>
> observation INT AUTO_INCREMENT,
> morph VARCHAR,
> date DATE,
> plot VARCHAR,
> number INT,
> comment TEXT,
> week VARCHAR
>
> observation is the primary key, and morph is a foreign key.
>
> How do I make a query such that I get all the latest observation
> 'dates' of every distinct 'morph'?

Search this group for "strawberry query"
Also read
http://dev.mysql.com/doc/refman/5.0/...group-row.html
(you want the LEFT JOIN example)

"Captain Paralytic" <paul_lautman@yahoo.com> schreef in bericht
news:b39176b1-e71d-4439-b09e-c08d0bebfaf5@d21g2000prf.googlegroups.com...
> On 18 Jan, 14:55, Plantky <ngokang...@gmail.com> wrote:
>> Hi,
>>
>> I have a table called "obs" with the following:
>>
>> observation INT AUTO_INCREMENT,
>> morph VARCHAR,
>> date DATE,
>> plot VARCHAR,
>> number INT,
>> comment TEXT,
>> week VARCHAR
>>
>> observation is the primary key, and morph is a foreign key.
>>
>> How do I make a query such that I get all the latest observation
>> 'dates' of every distinct 'morph'?
>
> Search this group for "strawberry query"
> Also read
> http://dev.mysql.com/doc/refman/5.0/...group-row.html
> (you want the LEFT JOIN example)


why not do:
SELECT morph, max(date) date FROM obs GROUP BY morph

On Fri, 18 Jan 2008 16:57:18 +0100, Luuk <luuk@invalid.lan> wrote:
> "Captain Paralytic" <paul_lautman@yahoo.com> schreef in bericht
> news:b39176b1-e71d-4439-b09e-c08d0bebfaf5@d21g2000prf.googlegroups.com...
>> On 18 Jan, 14:55, Plantky <ngokang...@gmail.com> wrote:
>>> Hi,
>>>
>>> I have a table called "obs" with the following:
>>>
>>> observation INT AUTO_INCREMENT,
>>> morph VARCHAR,
>>> date DATE,
>>> plot VARCHAR,
>>> number INT,
>>> comment TEXT,
>>> week VARCHAR
>>>
>>> observation is the primary key, and morph is a foreign key.
>>>
>>> How do I make a query such that I get all the latest observation
>>> 'dates' of every distinct 'morph'?
>>
>> Search this group for "strawberry query"
>> Also read
>> http://dev.mysql.com/doc/refman/5.0/...group-row.html
>> (you want the LEFT JOIN example)
>
>
> why not do:
> SELECT morph, max(date) date FROM obs GROUP BY morph

Could be done, but not if you want the field 'observation' in there too.
So either solution could be valid, depending on what the OP really needs.
--
Rik Wasmus

Thanks Luuk, it works!!