Re: ROW_NUMBER() and SQL syntax

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Mon, 17 Aug 2009 10:04:28 +0200
Message-ID: <h6b2ue$6jm$1_at_news.eternal-september.org>

Maxim Demenko escribió:

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

I can't manage to get the explain plan on this _at_#$%& 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
--

Received on Mon Aug 17 2009 - 03:04:28 CDT