[thelist] SQL question
Mattias Thorslund
mattias at thorslund.us
Fri Jul 14 02:17:19 CDT 2006
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
More information about the thelist
mailing list