Solved

Need help on sql

Posted on 2014-10-28
7
241 Views
Last Modified: 2014-10-31
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
0
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 40409059
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40409128
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 150 total points
ID: 40409819
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40409825
>>"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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40410642
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
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 100 total points
ID: 40415198
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
 

Author Closing Comment

by:sam_2012
ID: 40416231
awesome.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question