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