[Stocker1216]

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

[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
)

[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 desc) dr2
where dr.rowid = dr2.rowid
  and rownum <= 2 


[Stocker1216]

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

[Stocker1216]

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 desc) dr2
where dr.rowid = dr2.rowid
  and rownum <= 2 


This returns the first two rows of the dr2 subquery

[Stocker1216]

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)

[andrewst]

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)

[alberto.dellera]

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

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

[Stocker1216]

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!!!

[alberto.dellera]

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