Oracle SQL - achieve MAX functionality in where clause?

Question

Two tables:

ItemComment(ItemId, ItemComment, DateCommentPosted)
Item (ItemId, ItemName, ItemPrice) 

I need itemname and price of items that have not received any comment since say last 6 months.

I know this won't work:

SELECT i.itemid, i.name, i.price 
  FROM Item i, ItemComment c
 WHERE i.itemid = c.itemid 
   AND Max(c.dateCommentPosted) < (add_months(SYSDATE,-6));

There are answers to similar things on StackOverflow, but Oracle is not liking it. Any Oracle-specific thing?

Answer 1

Using the preferred explicit JOIN notation and the GROUP BY and HAVING clauses, you should be able to use:

SELECT i.itemid, i.name, i.price 
  FROM Item i
  JOIN ItemComment c ON i.itemid = c.itemid
 GROUP BY i.itemid, i.name, i.price
HAVING MAX(c.dateCommentPosted) < (ADD_MONTHS(SYSDATE, -6));

You could also write a sub-query, either using a WITH clause or directly in the FROM list, to calculate the most recent date that a comment was posted for each item and then join that with the Item table.

You might want to consider whether items without any comments should be selected or not.

What if I want to show/select Max(c.dateCommentPosted) too. Is that an option? If I add that in the select clause and add dateCommentPosted in the GROUP BY clause, would that be correct?

Add it to the select clause, but not the GROUP BY clause:

SELECT i.itemid, i.name, i.price, MAX(c.dateCommentPosted) AS dateCommentPosted
  FROM Item i
  JOIN ItemComment c ON i.itemid = c.itemid
 GROUP BY i.itemid, i.name, i.price
HAVING MAX(c.dateCommentPosted) < (ADD_MONTHS(SYSDATE, -6));

You might prefer to leave the AS out. AFAIK, Oracle absolutely rejects AS in table aliases (so I left it out there), but it accepts (but does not require) it in 'column aliases' in the select-list.

Incidentally, the SQL Standard, and therefore 'all' SQL DBMS, require aggregate comparisons in the HAVING clause, and the HAVING clause requires a GROUP BY clause. Hence, GBH — Group By / Having as well as Grievous Bodily Harm.