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 regards, Karol Bieniaszewski W dniu 2013-08-09 12:45:29 użytkownik Adriano dos Santos Fernandes <adrian...@gmail.com> napisał: > Put in your head that: > 1) you can have more than one column using ROW_NUMBER, each which > different order > 2) no implicit order will be assumed when only one column using > ROW_NUMBER is present > > then all your questions will be answered. > > > Adriano > > > On 09/08/2013 03:25, liviuslivius wrote: > > Hi, > > > > i post this question on support group by may be this is better place > > for this question, because it is about new feature in FB3.0 > > my English is not good then maybe i put example about what i saying > > look at this simple table and unordered inserted data > > > > CREATE TABLE TEST > > ( > > ID integer NOT NULL PRIMARY KEY > > ); > > > > commit; > > > > INSERT INTO TEST (ID) VALUES (2); > > INSERT INTO TEST (ID) VALUES (1); > > INSERT INTO TEST (ID) VALUES (4); > > INSERT INTO TEST (ID) VALUES (3); > > INSERT INTO TEST (ID) VALUES (5); > > > > commit; > > > > ################ simple select to show data order in page > > ######################## > > > > SELECT T.ID FROM TEST T > > > > [ID] > > 2 > > 1 > > 4 > > 3 > > 5 > > > > ############### now row_number without order in query and in over() > > ######################### > > > > SELECT T.ID, row_number() over() FROM TEST T > > [ID][ROW_NUMBER] > > 21 > > 12 > > 43 > > 34 > > 55 > > > > ??????????????? now row_number with order in query asc and nothing in > > over() ??????????????? > > > > should this query resultset look like this or row_number column > > should be sequential 1, 2, 3, 4, 5? > > SELECT T.ID, row_number() over() FROM TEST T order by T.ID ASC > > [ID][ROW_NUMBER] > > 12 > > 21 > > 34 > > 43 > > 55 > > > > ??????????????? now row_number with order in query desc and nothing in > > over() ??????????????? > > should this query resultset look like this or row_number column > > should be sequential 1, 2, 3, 4, 5? > > SELECT T.ID, row_number() over() FROM TEST T order by T.ID DESC > > [ID][ROW_NUMBER] > > 55 > > 43 > > 34 > > 21 > > 12 > > > > ######################################## > > this i suppose is ok we specify order in over() (interesting that > > resultset is also ordered by ID column asc) > > SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T > > [ID][ROW_NUMBER] > > 11 > > 22 > > 33 > > 44 > > 55 > > > > ######################################## > > this i suppose is ok we specify order in over() (interesting that > > resultset is also ordered by ID column desc) > > SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T > > [ID][ROW_NUMBER] > > 51 > > 42 > > 33 > > 24 > > 15 > > > > ######################################## > > this i suppose is ok we specify order in over() and in query > > SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T ORDER BY > > T.ID ASC > > [ID][ROW_NUMBER] > > 11 > > 22 > > 33 > > 44 > > 55 > > > > ######################################## > > this i suppose is ok we specify order in over() and in query > > SELECT T.ID, row_number() over(order by T.ID ASC) FROM TEST T ORDER BY > > T.ID DESC > > [ID][ROW_NUMBER] > > 55 > > 44 > > 33 > > 22 > > 11 > > > > ######################################## > > this i suppose is ok we specify order in over() and in query > > SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T ORDER > > BY T.ID ASC > > [ID][ROW_NUMBER] > > 15 > > 24 > > 33 > > 42 > > 51 > > > > ######################################## > > this i suppose is ok we specify order in over() and in query > > SELECT T.ID, row_number() over(order by T.ID DESC) FROM TEST T ORDER > > BY T.ID DESC > > [ID][ROW_NUMBER] > > 51 > > 42 > > 33 > > 24 > > 15 > > > > 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 > > > ------------------------------------------------------------------------------ > 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 > ------------------------------------------------------------------------------ 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
