How to get latest date for each distinct entry?
This is a discussion on How to get latest date for each distinct entry? within the MySQL forums, part of the Database Server Software category; --> Hi, I have a table called "obs" with the following: observation INT AUTO_INCREMENT, morph VARCHAR, date DATE, plot VARCHAR, ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 11:32 AM
| ||||
| ||||
 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'?      |
|
02-28-2008, 11:32 AM
| |||
| |||
| Re: How to get latest date for each distinct entry? 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) |
|
02-28-2008, 11:32 AM
| |||
| |||
| Re: How to get latest date for each distinct entry? "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 |
|
02-28-2008, 11:32 AM
| |||
| |||
| Re: How to get latest date for each distinct entry? 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 |
Linear Mode
