Need help on sql

Hi ,

I have an table called product , Sub_product , comments . I need to pick the latest comment added to an product in an report query.
Product :
Product Id number
Product Name varchar2(4000);

Sub_product
Product_id  number
Sub_product_id number
sub_product_name varchar2(4000);


comments

Comment_id number
comment clob (maximum of 4500 chars only)
created_date  date
last_modified_date date
product_id number
sub_product_id number

Query used:

Select
p.product_id,
t.sub_product_id,
c_sub_product.comments
FROM PRODUCT p
LEFT OUTER JOIN SUB_PRODUCT t ON (t.product_id=p.product_id)
LEFT OUTER JOIN comments c_sub_product
       ON(t.c_sub_product_id = c_sub_product.sub_product_id
       AND p.product_id = c_sub_product.product_id
       AND(1,    c_sub_product.comment_id) IN
                                        (SELECT row_number() over(PARTITION BY c_ita.sub_product_id
                                         ORDER BY c_ita.last_modified_date DESC),    c_ita.comment_id
                                         FROM comments c_ita
                                         WHERE c_ita.c_sub_product_Id = t.sub_product_id)) ;

                     
Data :

Product :
product_id  PRODUCT_NAME
1                     xyz
2                     abc
3                      zed

SuB_PRODUCT
sub_product_id product_id  sub_product_name
1                             1                    zyx-asas
2                             1                      asasas
3                             2                sasdasd
4                             3                        sddfsdfdf

comments:

Comment_id               comments             created_date                     last_modified_date date  product_id  sub_product_id

  1                                    sdsad                  01-01-2014   10:12 :01            01-01-2014 10:12:01             1               1
  2                                     aasdasd            01-01-2014   10:12 :02            01-01-2014 10:12:02              1               1


The query used above takes time , any help for an alternate query is really appreciated.
Output should be :

product_id sub_product_id comments
1                           1                        aasdasd
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
SELECT product_id, sub_product_id, comments
  FROM (SELECT c.product_id,
               c.sub_product_id,
               c.comments,
               ROW_NUMBER()
               OVER(PARTITION BY c.product_id, c.sub_product_id ORDER BY c.last_modified_date DESC)
                   rn
          FROM product p, sub_product s, comments c
         WHERE p.product_id = s.product_id
           AND c.product_id = s.product_id
           AND c.sub_product_id = s.sub_product_id)
 WHERE rn = 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
the above assumes your example is simplified and you might want to include some other columns from the product or sub_product tables

if not, and you only need the columns from the comments table you can simplify even more by removing the other tables completely.


SELECT product_id, sub_product_id, comments
  FROM (SELECT c.product_id,
               c.sub_product_id,
               c.comments,
               ROW_NUMBER()
               OVER(PARTITION BY c.product_id, c.sub_product_id ORDER BY c.last_modified_date DESC)
                   rn
          FROM comments c)
 WHERE rn = 1
0
PortletPaulfreelancerCommented:
In your original query you use LEFT JOINS, so a similar construct would be:
SELECT
      p.product_id
    , s.sub_product_id
    , c.comments
FROM product p
      LEFT OUTER JOIN sub_product s
                  ON p.product_id = s.product_id
      LEFT OUTER JOIN (
                  SELECT
                        product_id
                      , sub_product_id
                      , comments
                      , ROW_NUMBER() OVER (PARTITION BY product_id, sub_product_id
                        ORDER BY last_modified_date DESC)
                        AS rn
                  FROM comments
            ) c
                  ON s.product_id = c.product_id
                        AND s.sub_product_id = c.sub_product_id
                        AND c.rn = 1
;

Open in new window

which produces:
| PRODUCT_ID | SUB_PRODUCT_ID | COMMENTS |
|------------|----------------|----------|
|          1 |              1 |  aasdasd |
|          3 |              4 |   (null) |
|          1 |              2 |   (null) |
|          2 |              3 |   (null) |

Open in new window

If you ONLY want records where comments exist, then use an INNER JOIN to the subquery c
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
>>"The query used above takes time "
I assume the fields used in the joins are indexed. Some performance gains may be possible by using INNER instead of LEFT joins.

Provide an explain plan (as text, not image) for whatever query you decide is functionally correct if performance remains an issue.
0
sdstuberCommented:
both of the queries I posted are functionally correct - that is, they both produce the requested results.

The second one is, obviously, the most efficient since it eliminates all the extraneous stuff.  The "c" alias isn't needed, that's just a carryover from my first post.  But, as noted above, even though it's the most efficient for returning the specified results, it has limitations in what you can do to expand on it.

You could alternately use RANK or DENSE_RANK instead of ROW_NUMBER but those count in special ways that don't really apply here.
0
Geert GOracle dbaCommented:
why not use the last_value analytic function ?

select distinct
p.product_id,
t.sub_product_id,
last_value(c.comments) over (partition by p.product_id, t.sub_product_id
  order by p.product_id, t.sub_product_id, c.last_modified_date aSC nulls first) last_comments
FROM PRODUCT p
  LEFT OUTER JOIN SUB_PRODUCT t ON (t.product_id=p.product_id)
  LEFT OUTER JOIN comments c
       ON(t.c_sub_product_id = c.sub_product_id
       AND p.product_id = c.product_id)

Open in new window


or first_value

select distinct
p.product_id,
t.sub_product_id,
first_value(c.comments) over (partition by p.product_id, t.sub_product_id
  order by p.product_id, t.sub_product_id, c.last_modified_date DESC nulls last) last_comments
FROM PRODUCT p
  LEFT OUTER JOIN SUB_PRODUCT t ON (t.product_id=p.product_id)
  LEFT OUTER JOIN comments c
       ON(t.c_sub_product_id = c.sub_product_id
       AND p.product_id = c.product_id)

Open in new window

0
sam_2012Author Commented:
awesome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.