Re: ROW_NUMBER() and SQL syntax

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 14 Aug 2009 19:21:39 +0200
Message-ID: <4A859D23.8080607_at_gmail.com>

Álvaro G. Vicario wrote:
> 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;
>
>
>

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 Received on Fri Aug 14 2009 - 12:21:39 CDT