ROW_NUMBER() and SQL syntax

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Fri, 14 Aug 2009 14:53:35 +0200
Message-ID: <h63n8f$g9e$1_at_news.eternal-september.org>

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

Received on Fri Aug 14 2009 - 07:53:35 CDT