Databases
StackOverflow2013
Postsdbo
POOracle - How to filter out duplicate rows without using GROUP BY
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle - How to filter out duplicate rows without using GROUP BY
    text
    copied!<p>I have the following query:</p> <pre><code>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') </code></pre> <p>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:</p> <pre><code>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 </code></pre> <p>However, now I can't <code>JOIN</code> anything in. For example, if I try:</p> <pre><code>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 </code></pre> <p>I'll get the error:</p> <blockquote> <p>ORA-00979: not a GROUP BY expression</p> </blockquote> <p>Is there a better way to filter out duplicate changes to the same project?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload