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
