GROUP BY with multiple columns
Hi guys,
I have the following two tables:
CLIP with columns id, name, duration, statusID
STATUS_LOOKUP with columns id, clipID, statusID, dateAdded
The latter is a lookup table used to track status changes in the CLIP table. Whenever statusID of an entry in the CLIP table changes, a new entry with the corresponding clipID, statusID and a timestamp of Now() is added to the lookup table.
Due CLIP entries going back and forth between statuses, we have multiple entries with the same status for the same clipID in the lookup table.
We wish to do a query to get a CLIPs entries in the lookup table, one per statusID. The following query gets what we want, but including all duplicates:
SELECT C.id, name, duration, statusID, dateAdded, clipID
FROM clip C, clipStatusLookup LKP
WHERE C.id = LKP.clipID
<cfif IsDefined("arguments.startDate") and IsDefined("arguments.endDate")>
AND LKP.dateAdded >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#arguments.startDate#" />
AND LKP.dateAdded <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#arguments.endDate#" />
</cfif>
We wish to get the above results, but filter out the duplicate entries in the lookup table (entries with the same clipID and same statusID). The following query uses group by to filter out the duplicates:
SELECT MAX(id) AS id, clipID, statusID
FROM clipStatusLookup
GROUP BY statusID, clipID
ORDER BY clipID
However, if i try to select additional columns (name, duration, dateAdded), I get this error:
Column 'clip.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Can anyone provide pointers on how to pull out all columns while still filtering out duplicates?
All help is greatly appreciated!
Thank you!