W dniu 2013-08-10 09:39:43 użytkownik Mark Rotteveel <m...@lawinegevaar.nl> napisał: > On 10-8-2013 09:08, liviuslivius wrote: > > Hi, > > > > you do not understand me correctly > > i talking about query with over() not over(order by) > > i know that i can specify many row_number clause in one query > > > > Firebird allow this construction over(without order by) and then it should > > numerate records in resultset sequential > > Example MSSQL disallow row_number with over(without order by) > > this is message from MSSQL > > "The function 'row_number' must have an OVER clause with ORDER BY." > > > > If Firebird allow this structure, then this two queries should always > > numerate resultset not some internal order of records > > e.g. > > SLECT T.ID, row_number() over() FROM TEST T order by T.ID ASC <-- asc > > [ID][ROW_NUMBER] > > 1 1 > > 2 2 > > 3 3 > > 4 4 > > 5 5 > > > > SELECT T.ID, row_number() over() FROM TEST T order by T.ID DESC <-- desc > > [ID][ROW_NUMBER] > > 5 1 > > 4 2 > > 3 3 > > 2 4 > > 1 5 > > > > but now Firebird return row_number like this > > > > SELECT T.ID, row_number() over() FROM TEST T order by T.ID ASC > > [ID][ROW_NUMBER] > > 1 2 > > 2 1 > > 3 4 > > 4 3 > > 5 5 > > > > this should be fixed or disallowed by Firebird like MSSQL > > According to the SQL Specification (as far as I read it correctly) you > are not entirely correct, it says SQL:2011 Foundation, 6.10 <window > function>: > > " > 7) If <ntile function>, <lead or lag function>, <rank function type> or > ROW_NUMBER is specified, then: > a) If <ntile function>, <lead or lag function>, RANK or DENSE_RANK is > specified, then the window ordering clause WOC of WDX shall be present. > ... > " > > In other words, ROW_NUMBER does not require a window ordering clause. > The specification also says that ROW_NUMBER is not deterministic. In > other words: you should not expect any specific ordering of row_number > if you did not specify an order by in the window and in the query itself. > > However it also says (under Conformance Rules): > " > 4) Without Feature T612, “Advanced OLAP operations”, conforming SQL > language shall not contain a <window function> that simply contains > ROW_NUMBER and immediately contains a <window name or specification> > whose window structure descriptor does not contain a window ordering clause. > " > > I am not sure how to read this though, because I believe they actually > mean: "to implement feature T612, you must have ...". > -- > Mark Rotteveel >
Hi, i read now it also and find something line like this row_number() over WNS i equivalent to: count(*) over (WNS1 ROWS UNBOUNDED PRECEDING) 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" regards, Karol Bieniaszewski ------------------------------------------------------------------------------ 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
