MySQL Forums
Forum List  »  Newbie

GROUP BY with multiple columns
Posted by: Aksel Gresvig
Date: February 24, 2009 06:25PM

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!

Options: ReplyQuote


Subject
Written By
Posted
GROUP BY with multiple columns
February 24, 2009 06:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.