Solved

Need help on sql

Posted on 2014-10-28
7
227 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
7 Comments
 
LVL 73

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 73

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 48

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

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 73

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 36

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now