MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 6 2004 12:47pm
Subject:Re: sql query to return unique ids from a table of date stamped results
View as plain text  
For each ADNO, you want the row with Lastupdatetime equal to that group's 
MAX(Lastupdatetime) .  This is a little bit tricky and a frequently asked 
question.  There are 3 ways to do it documented in the manual 
<http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html>.

Michael

Rob Keeling wrote:

> I am trying to find the sql statement needed to extract, from a table of
> data with multiple instances of a id no,
> a list of unique id nos, picking the latest (by datestamp which is stored as
> a second field) so that a master list is updated.
> 
> The application is a list of student photos, each database row defines the
> filename of a photo, the student id is assigned to the
> photo once it is known, I then need to produce a list for all photos showing
> the details of the latest photo for each student.
> 
> SELECT  *  FROM Photosforimport ORDER  BY adno, Lastupdatetime DESC
> 
> Gives me the data I need, however I need to be able to just pick out the row
> with the latest Lastupdatetime for each ADNO.
> 
> I couldn`t find anything on google, but may have been asking the wrong
> question!
> 
> Any help greatly appreciated.
> 
> Rob Keeling
> 
> 
Thread
sql query to return unique ids from a table of date stamped resultsRob Keeling6 Nov
  • Re: sql query to return unique ids from a table of date stamped resultsGleb Paharenko6 Nov
    • Re: sql query to return unique ids from a table of date stamped resultsMichael Stassen6 Nov
  • Re: sql query to return unique ids from a table of date stamped resultsMichael Stassen6 Nov