Geek asks and answers

Oracle SQL - implement MAX functionality in where clause?

Two tables:

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

I need the name of the item and the price of items that have not received any comment, as they say the last 6 months.

I know this will not 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 in StackOverflow, but Oracle doesn't like it. Any thing specific to Oracle?

+3
source share
4 answers

Try the following:

select i.itemid, i.name, i.price
  from Item i 
 where not exists (
     select 1
       from ItemComment c
      where c.dateCommentPosted between (SYSDATE - 180) and SYSDATE
        and c.itemid = i.itemid
 )
+2
source

Using the preferred explicit JOIN notation and 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));

-, WITH, FROM, , , .

, , - .

, / Max(c.dateCommentPosted) . ? select dateCommentPosted GROUP BY, ?

select, GROUP BY:

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));

, AS. AFAIK, Oracle AS ( ), ( ) " " .

, SQL Standard , , "" SQL- HAVING, HAVING GROUP BY. , GBH — /, .

+9

:

with cte as (
  select i.itemid,
    max(DateCommentPosted) as LastCommentDate
  from Item i left join ItemComment c on c.itemid = i.itemid
  group by i.itemid
)
select i.itemid, i.ItemName, i.ItemPrice 
from Item i join cte c on c.itemid = i.itemid
where LastCommentDate is null or LastCommentDate < add_months(SYSDATE,-6)

sqlfiddle . .

+3

, , , , HAVING, WHERE

http://psoug.org/reference/group_by.html

+1

More articles:


All Articles