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

[liviuslivius <liv...@po...>]

W dniu 2013-08-10 09:39:43 użytkownik Mark Rotteveel <ma...@la...> 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