Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2002
    Posts
    6

    Unanswered: Correlated top-n subquery

    In Oracle 9i:

    Observe the following query which (in theory) returns only the rows from documentrev that contain the maximum revision_number per document id. Because of the syntax for the top-N query in Oracle I have to perform two subqueries. When I get the second level deep in the subqueries Oracle looses track or the correlation name “dr”. Do you know any trick to get around this (or perhaps an alternative top-N syntax)?


    select * from t2_dev.documentrev dr
    where dr.rowid =
    (select * from
    (
    select rowid from t2_dev.documentrev dr2
    where
    dr.document_id = dr2.document_id
    and dr2.user_id > -1
    order by dr2.revision_number desc
    )
    where rownum <2
    )

    This works in SQL Server using the TOP-N syntax

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    select * from t2_dev.documentrev dr
    where 2 >= (
    select count(*) from t2_dev.documentrev dr2
    where dr.document_id = dr2.document_id
    and dr2.user_id > -1
    and dr2.revision_number >= dr.revision_number
    )

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    PHP Code:
    select dr.* 
    from t2_dev.documentrev dr,
         (
    select rowid
          from t2_dev
    .documentrev d
          where d
    .user_id > -1
          order by d
    .revision_number descdr2
    where dr
    .rowid dr2.rowid
      
    and rownum <= 

  4. #4
    Join Date
    Jan 2002
    Posts
    6

    thanks, but

    Quote Originally Posted by shoblock
    select * from t2_dev.documentrev dr
    where 2 >= (
    select count(*) from t2_dev.documentrev dr2
    where dr.document_id = dr2.document_id
    and dr2.user_id > -1
    and dr2.revision_number >= dr.revision_number
    )
    This returns a list of documents where the document has multiple revisions... not the same query

  5. #5
    Join Date
    Jan 2002
    Posts
    6

    even colder

    Quote Originally Posted by carloa
    PHP Code:
    select dr.* 
    from t2_dev.documentrev dr,
         (
    select rowid
          from t2_dev
    .documentrev d
          where d
    .user_id > -1
          order by d
    .revision_number descdr2
    where dr
    .rowid dr2.rowid
      
    and rownum <= 
    This returns the first two rows of the dr2 subquery

  6. #6
    Join Date
    Jan 2002
    Posts
    6
    I did come up with one alternative (if this gives anyone any hints) and on the assumption revision number is unique but it is actually quite slow:

    select dr.*
    from t2_DEV.DOCUMENTREV DR
    ,(SELECT DOCUMENT_ID, MAX(REVISION_NUMBER) AS MAX_REV_NBR FROM T2_DEV.DOCUMENTREV GROUP BY DOCUMENT_ID) DR_S
    WHERE DR.DOCUMENT_ID = DR_S.DOCUMENT_ID
    AND DR.REVISION_NUMBER = DR_S.MAX_REV_NBR;

    As ugly as this is it was faster than that above:
    select dr.*
    from t2_DEV.DOCUMENTREV DR
    where document_id, revision_number in
    (SELECT DOCUMENT_ID, MAX(REVISION_NUMBER) AS MAX_REV_NBR FROM T2_DEV.DOCUMENTREV GROUP BY DOCUMENT_ID)

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Another alternative:

    select dr.*
    from t2_DEV.DOCUMENTREV DR
    where revision_number =
    (SELECT MAX(REVISION_NUMBER) FROM T2_DEV.DOCUMENTREV DR2
    WHERE DR2.DOCUMENT_ID = DR.DOCUMENT_ID)

  8. #8
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    analytics

    Why not using analytics for this top-N ? They were (mostly) designed for this purpose and are usually much faster than a correlated subquery ..

    Code:
    SQL> create table documentrev (document_id,user_id,revision_number) as
           select mod(rownum,3),1,rownum from all_object
           where rownum <= 10;
    
    Table created.
    
    SQL> select * from documentrev;
    
    DOCUMENT_ID    USER_ID REVISION_NUMBER
    ----------- ---------- ---------------
              1          1               1
              2          1               2
              0          1               3
              1          1               4
              2          1               5
              0          1               6
              1          1               7
              2          1               8
              0          1               9
              1          1              10
    
    10 rows selected.
    
    SQL> select *
      2    from (
      3  select document_id,user_id,revision_number,
      4         rank() over (partition by document_id 
                                 order by revision_number desc) rnk
      5    from  documentrev
      6  )
      7   where rnk = 1;
    
    DOCUMENT_ID    USER_ID REVISION_NUMBER        RNK
    ----------- ---------- --------------- ----------
              0          1               9          1
              1          1              10          1
              2          1               8          1

  9. #9
    Join Date
    Jan 2002
    Posts
    6

    Thumbs up Analytical functions ROCK!

    Alberto. This is absolutely the correct answer! I stumbled across the analytical functions a few days ago. I don't believe the last version of Oracle I worked with (8.04) did not have them so I was unfamiliar with them. Not only are these FAST but are extremely flexible!!!

  10. #10
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Smile

    There's a certain SQL guru that says that "Analytics are the coolest thing to happen after the SELECT statement" :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •