?
Solved

Need help on sql

Posted on 2014-10-28
7
Medium Priority
?
242 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 1000 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 600 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 400 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

777 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