[thelist] SQL question

[Mattias Thorslund]

This time, a question on the topic of SQL, and MySQL in particular.

I have a table from which I want to retrieve the ID value associated
with the record that contains the MAX() value of a different column.
This is complicated further by the fact that I need to GROUP BY a third
column.

Example Table:
----------------
RecordID     (Primary key)
TheDate      (used in MAX() function)
GroupID      (used in GROUP BY)

Example Data:
--------------------------------
RecordID   TheDate     GroupID
1          2006-04-19  2
2          2006-06-16  1
3          2006-06-02  1
4          2006-07-12  2
5          2006-07-12  2


The following query does not guarantee that the returned RecordID is the
record ID of the row that contained the latest date in each group:

SELECT
   RecordID,
   MAX(TheDate) AS LatestDate,
   GroupID
FROM
   Example
GROUP BY
   GroupID


Using the sample data above, I would want to see the following result:

RecordID   LatestDate   GroupID
3          2006-06-02   1
5          2006-07-12   2

But the returned value for RecordID will be any of the record IDs that
match each group, and this is a likely result (note the un-matching
RecordID values):

RecordID   LatestDate   GroupID
2          2006-06-02   1
1          2006-07-12   2


I've used a sub-query, and matching the max date with the date value in
order to make this work, but I think it's a pretty ugly solution:

SELECT
   Example.RecordID,
   SubQuery.LatestDate
FROM
   Example
   INNER JOIN (
      select
         max(thedate) as latestdate,
         groupid
      from
         example
      group by
         groupid
   ) AS SubQuery ON (
      Example.GroupID = SubQuery.GroupID
      AND Example.TheDate = SubQuery.LatestDate
   )
GROUP BY Example.GroupID;


The GROUP BY clause in the outer statement eliminates duplicates for
each group which could otherwise happen when the same group has two
records with the same date.


Does anyone have ideas on how to write a valid SQL statement that
returns the correct data?

Many thanks in advance.

Mattias