Re: [Firebird-devel] row_number window function with over()

[Dmitry Yemanov]

10.08.2013 21:20, liviuslivius пишет:
>
> and i test this also against Oracle 11g
> and try SELECT T.ID, row_Number() over() FROM TEST
> i get error the same as in MSSQL
> "Error: ORA-30485: missing ORDER BY expression in the window specification"

PGSQL 9.1:

postgres=# select id from test;
  id
----
   2
   1
   4
   3
   5
(5 rows)

postgres=# select id, row_number() over() from test;
  id | row_number
----+------------
   2 |          1
   1 |          2
   4 |          3
   3 |          4
   5 |          5
(5 rows)

postgres=# select id, row_number() over() from test order by id asc;
  id | row_number
----+------------
   1 |          2
   2 |          1
   3 |          4
   4 |          3
   5 |          5
(5 rows)

postgres=# select id, row_number() over() from test order by id desc;
  id | row_number
----+------------
   5 |          5
   4 |          3
   3 |          4
   2 |          1
   1 |          2
(5 rows)

postgres=# select id, row_number() over(order by id asc) from test;
  id | row_number
----+------------
   1 |          1
   2 |          2
   3 |          3
   4 |          4
   5 |          5
(5 rows)

postgres=# select id, row_number() over(order by id desc) from test;
  id | row_number
----+------------
   5 |          1
   4 |          2
   3 |          3
   2 |          4
   1 |          5
(5 rows)

postgres=# select id, row_number() over(order by id asc) from test order 
by id asc;
  id | row_number
----+------------
   1 |          1
   2 |          2
   3 |          3
   4 |          4
   5 |          5
(5 rows)

postgres=# select id, row_number() over(order by id asc) from test order 
by id desc;
  id | row_number
----+------------
   5 |          5
   4 |          4
   3 |          3
   2 |          2
   1 |          1
(5 rows)

postgres=# select id, row_number() over(order by id desc) from test 
order by id asc;
  id | row_number
----+------------
   1 |          5
   2 |          4
   3 |          3
   4 |          2
   5 |          1
(5 rows)

postgres=# select id, row_number() over(order by id desc) from test 
order by id desc;
  id | row_number
----+------------
   5 |          1
   4 |          2
   3 |          3
   2 |          4
   1 |          5
(5 rows)


Dmitry



------------------------------------------------------------------------------
Get 100% visibility into Java/.NET code with AppDynamics Lite!
It's a free troubleshooting tool designed for production.
Get down to code-level detail for bottlenecks, with <2% overhead. 
Download for free and get started troubleshooting in minutes. 
http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel