[thelist] SQL question

[Tab Alleman]

Maybe something like this?:

SELECT
	RecordID
,	TheDate
,	GroupID
FROM Example e
WHERE e.RecordID=(
	SELECT TOP 1 RecordID
	FROM Example e2
	WHERE e.GroupID=e2.GroupID
	ORDER BY TheDate DESC
)
GROUP BY GroupID


> -----Original Message-----
> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of Mattias Thorslund
> Sent: Friday, July 14, 2006 3:17 AM
> To: thelist at lists.evolt.org
> Subject: [thelist] SQL question
> 
> 
> 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
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
>