I've created this view:

CREATE VIEW latest_b AS
SELECT a_id, b_id
FROM (
SELECT a.a_id, a.b_id,
ROW_NUMBER() OVER (
PARTITION BY a.a_id
ORDER BY b.start_date DESC, b.end_date DESC, a.b_id DESC
) AS b_order
FROM a
INNER JOIN b ON a.b_id=b.b_id
)
WHERE b_order=1

So far so good (it returns the expected results). I'm just curious about
a detail. Do I actually need a subquery to remove all rows where B_ORDER
is greater than one?

I can't manage to add WHERE, GROUP BY or HAVING clauses to the main
query and still get valid SQL. The subquery works fine but I wonder
whether I'm overcomplicating it :-?


It should work in Oracle Oracle9i Enterprise Edition Release 9.2.0.1.0.


Note: The example for ROW_NUMBER() in the Oracle manual uses a subquery
as well:

SELECT last_name FROM
(SELECT last_name, ROW_NUMBER() over (order by last_name) R FROM
employees)
WHERE R BETWEEN 51 and 100;



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On Aug 14, 7:53*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
It does so for good reason:

SQL> SELECT ename FROM
2 (SELECT ename, ROW_NUMBER() over (order by ename) R FROM
3 emp)
4 WHERE R BETWEEN 5 and 10;

ENAME
----------
FORD
JAMES
JONES
KING
MARTIN
MILLER

6 rows selected.

SQL>
SQL> SELECT ename, ROW_NUMBER() over (order by ename) R FROM
2 emp
3 WHERE ROW_NUMBER() over (order by ename) BETWEEN 5 and 10;
WHERE ROW_NUMBER() over (order by ename) BETWEEN 5 and 10
*
ERROR at line 3:
ORA-30483: window functions are not allowed here


SQL>


David Fitzjarrell

Álvaro G. Vicario wrote:
Regarding the subquery question - David already provided you with
correct answer, however your query with analytical functions looks for
me as not the right tool, indeed it is rather an aggregate what you
need, so you can try with

select
a.a_id,
max(a.b_id) keep(dense_rank first order by b.start_date desc,b.end_date
desc)
from a,b
where a.b_id=b.b_id
group by a.a_id

Best regards

Maxim

Maxim Demenko escribió:

I can't manage to get the explain plan on this @#$%& system but your
alternative seems to be faster when run on current data. Thank you for
the tip.



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--