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]
2 1
1 2
4 3
3 4
5 5
??????????????? 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]
1 2
2 1
3 4
4 3
5 5
??????????????? 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]
5 5
4 3
3 4
2 1
1 2
########################################
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]
1 1
2 2
3 3
4 4
5 5
########################################
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]
5 1
4 2
3 3
2 4
1 5
########################################
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]
1 1
2 2
3 3
4 4
5 5
########################################
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]
5 5
4 4
3 3
2 2
1 1
########################################
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]
1 5
2 4
3 3
4 2
5 1
########################################
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]
5 1
4 2
3 3
2 4
1 5
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
