 | |
#1
 
| |||
| |||
|
Rownum -
01-04-2008
, 05:11 AM
#2
| |||
| |||
|
|
Hi, Did anyone knows a database system which can execute the following query: select * from (select rownum t, a, b, c, d from datatable where c=1 order by a,b,c) where t=15 In Oracle I get the 15th row of the subquery, but I cannot find another system which has implemented the rownum functionality like Oracle. Ideas? |
porting from Oracle. However, it is much better to use standard sql to
express the same thing:
select * from (
select row_number() over (order by a,b,c) as t, a,b,c,d from
datatable
where c=1
) as X where t=15
The name of the subquery is not necessary according to the standard,
but DB2 (V9) requires it.
HTH
/Lennart
#3
| |||
| |||
|
|
Hi, Did anyone knows a database system which can execute the following query: select * from (select rownum t, a, b, c, d from datatable where c=1 order by a,b,c) where t=15 In Oracle I get the 15th row of the subquery, but I cannot find another system which has implemented the rownum functionality like Oracle. Ideas? |
porting from Oracle. However, it is much better to use standard sql to
express the same thing:
select * from (
select row_number() over (order by a,b,c) as t, a,b,c,d from
datatable
where c=1
) as X where t=15
The name of the subquery is not necessary according to the standard,
but DB2 (V9) requires it.
HTH
/Lennart
#4
| |||
| |||
|
|
Hi, Did anyone knows a database system which can execute the following query: select * from (select rownum t, a, b, c, d from datatable where c=1 order by a,b,c) where t=15 In Oracle I get the 15th row of the subquery, but I cannot find another system which has implemented the rownum functionality like Oracle. Ideas? |
porting from Oracle. However, it is much better to use standard sql to
express the same thing:
select * from (
select row_number() over (order by a,b,c) as t, a,b,c,d from
datatable
where c=1
) as X where t=15
The name of the subquery is not necessary according to the standard,
but DB2 (V9) requires it.
HTH
/Lennart
#5
| |||
| |||
|
|
Hi, Did anyone knows a database system which can execute the following query: select * from (select rownum t, a, b, c, d from datatable where c=1 order by a,b,c) where t=15 In Oracle I get the 15th row of the subquery, but I cannot find another system which has implemented the rownum functionality like Oracle. Ideas? Thanks a lot Andre |
abomination.
you are better off using the ranking functions as Lennart mentioned.
Ed
#6
| |||
| |||
|
|
Hi, Did anyone knows a database system which can execute the following query: select * from (select rownum t, a, b, c, d from datatable where c=1 order by a,b,c) where t=15 In Oracle I get the 15th row of the subquery, but I cannot find another system which has implemented the rownum functionality like Oracle. Ideas? Thanks a lot Andre |
abomination.
you are better off using the ranking functions as Lennart mentioned.
Ed
#7
| |||
| |||
|
|
Hi, Did anyone knows a database system which can execute the following query: select * from (select rownum t, a, b, c, d from datatable where c=1 order by a,b,c) where t=15 In Oracle I get the 15th row of the subquery, but I cannot find another system which has implemented the rownum functionality like Oracle. Ideas? Thanks a lot Andre |
abomination.
you are better off using the ranking functions as Lennart mentioned.
Ed
#8
| |||
| |||
|
|
On Jan 4, 6:11*am, Andre Rothe <andre.ro... (AT) imise (DOT) uni-leipzig.de wrote: Did anyone knows a database system which can execute the following query: select * from (select rownum t, a, b, c, d from datatable where c=1 order by a,b,c) where t=15 In Oracle I get the 15th row of the subquery, but I cannot find another system which has implemented the rownum functionality like Oracle. Ideas? Thanks a lot Andre ROWNUM is assigned BEFORE the ORDER BY. it is a kludge and IMHO an abomination. you are better off using the ranking functions as Lennart mentioned. |
opinion that it is an abomination. It *IS* an abomination.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#9
| |||
| |||
|
|
On Jan 4, 6:11*am, Andre Rothe <andre.ro... (AT) imise (DOT) uni-leipzig.de wrote: Did anyone knows a database system which can execute the following query: select * from (select rownum t, a, b, c, d from datatable where c=1 order by a,b,c) where t=15 In Oracle I get the 15th row of the subquery, but I cannot find another system which has implemented the rownum functionality like Oracle. Ideas? Thanks a lot Andre ROWNUM is assigned BEFORE the ORDER BY. it is a kludge and IMHO an abomination. you are better off using the ranking functions as Lennart mentioned. |
opinion that it is an abomination. It *IS* an abomination.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#10
| |||
| |||
|
|
On Jan 4, 6:11*am, Andre Rothe <andre.ro... (AT) imise (DOT) uni-leipzig.de wrote: Did anyone knows a database system which can execute the following query: select * from (select rownum t, a, b, c, d from datatable where c=1 order by a,b,c) where t=15 In Oracle I get the 15th row of the subquery, but I cannot find another system which has implemented the rownum functionality like Oracle. Ideas? Thanks a lot Andre ROWNUM is assigned BEFORE the ORDER BY. it is a kludge and IMHO an abomination. you are better off using the ranking functions as Lennart mentioned. |
opinion that it is an abomination. It *IS* an abomination.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.