Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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
0
sam_2012
Asked:
sam_2012
3 Solutions
 
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
 
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
 
PortletPaulCommented:
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
Industry Leaders: 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!

 
PortletPaulCommented:
>>"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 GruwezOracle 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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now