Re: ROW_NUMBER() and SQL syntax
From: ddf <oratune_at_msn.com>
Date: Fri, 14 Aug 2009 09:50:57 -0700 (PDT)
Message-ID: <44817707-594d-4a36-93b6-544622320a54_at_w6g2000yqw.googlegroups.com>
On Aug 14, 7:53 am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com.invalid> 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;
>
FORD
JAMES
JONES
KING
MARTIN
MILLER 6 rows selected.
Date: Fri, 14 Aug 2009 09:50:57 -0700 (PDT)
Message-ID: <44817707-594d-4a36-93b6-544622320a54_at_w6g2000yqw.googlegroups.com>
On Aug 14, 7:53 am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com.invalid> 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;
>
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>
> --
> --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
> --
David Fitzjarrell Received on Fri Aug 14 2009 - 11:50:57 CDT