0

I have the following query:

select C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, C.MODIFIEDDATE, V.NAME from TPM_PROJECTCHANGES C inner join TPM_PROJECTVERSION V ON C.PROJECTID = V.PROJECTID AND C.VERSIONID = V.VERSIONID where C.MODIFIEDDATE BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY')

I want to show all changes in a project between certain dates. This works fine. However, I have a lot of duplicates (such as if someone changed the same attribute on the same project 50 times). I want to filter those out and only show the most recent time a certain attribute was changed on the project. I can do something like this:

select C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, MAX(C.MODIFIEDDATE) from TPM_PROJECTCHANGES C having MAX(C.MODIFIEDDATE) BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY') group by C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID

However, now I can't JOIN anything in. For example, if I try:

select C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, MAX(C.MODIFIEDDATE), V.NAME from TPM_PROJECTCHANGES C inner join TPM_PROJECTVERSION V ON C.PROJECTID = V.PROJECTID AND C.VERSIONID = V.VERSIONID having MAX(C.MODIFIEDDATE) BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY') group by C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID

I'll get the error:

ORA-00979: not a GROUP BY expression

Is there a better way to filter out duplicate changes to the same project?

Improve this question
2
  • 1
    Try moving having after group_by Commented Jul 25, 2012 at 22:08
  • @rogal111 - The position of HAVING makes no difference. It seems Oracle (or SQL) doesn't support this, even though it logically makes sense. Commented Jul 25, 2012 at 22:28

3 Answers 3

Reset to default
2

You can also use analytic functions:

select PROJECTID, VERSIONID, MODIFIEDATTRIBUTEID, MODIFIEDDATE, Name from (select C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, C.MODIFIEDDATE, V.NAME, row_number() over (partition by C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, V.NAME order by C.MODIFIEDDATE desc) as seqnum from TPM_PROJECTCHANGES C inner join TPM_PROJECTVERSION V ON C.PROJECTID = V.PROJECTID AND C.VERSIONID = V.VERSIONID where C.MODIFIEDDATE BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY') ) t where seqnum = 1
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

+1 for the only working answer so far, however it seems like this is going to get very complicated very quickly, as I add more columns in. I'm worried about perf as well. I'll accept this if no better answers are posted.
1

The HAVING should be the last line on your query:

select C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, MAX(C.MODIFIEDDATE), V.NAME from TPM_PROJECTCHANGES C inner join TPM_PROJECTVERSION V ON C.PROJECTID = V.PROJECTID AND C.VERSIONID = V.VERSIONID group by C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, V.NAME having MAX(C.MODIFIEDDATE) BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY')
Improve this answer

2 Comments

Doesn't matter where it is. I get not a GROUP BY expression unless I remove the V.NAME from the SELECT list.
@MikeChristensen Added V.NAME to the answer
0

The fundamental rule of SQL query composition is the following. Wrap your working SQl query in parenthesis, and inline it as a view:

select V.NAME, IC.* from (/*working query against TPM_PROJECTCHANGES here*/ ) IC inner join TPM_PROJECTVERSION V ...

Regarding the syntax of your last query, group by list is expected to accommodate more columns, than your select clause, not less (excluding the aggregates, of course).

Improve this answer

4 Comments

You're saying you cannot include values on JOIN'ed tables unless those columns are also in your GROUP BY list? This is why I want to avoid GROUP BY: Because I'm joining in about 5 tables and including a ton of columns from those tables.
If you don't care about aggregation, why wouldn't DISTINCT suffice?
select distinct C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, C.MODIFIEDDATE, V.NAME from TPM_PROJECTCHANGES C inner join TPM_PROJECTVERSION V ON C.PROJECTID = V.PROJECTID AND C.VERSIONID = V.VERSIONID where C.MODIFIEDDATE BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY')
I do care about aggregation. I want to only show the latest time the property was updated.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.