Thread: "Oracle's ROWNUM"
Hi, is there any way to get the number of the tuple in the result set... in Oracle you can write select rownum, * from mytable; and then get (handmade example) : rownum | id | name ----------------------------- 1 | 27| John Dow 2 | 09 | Jane Dow 3 | 11 | Bugs Bunny Tia Svenne Krap -- Mail usenet@krap.dk - svenne@krap.dk - PGP key id : 0xDF484022 ICQ: 5434480 - http://www.krap.dk - http://www.krap.net PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022
I think you should look into the documentation for your particular interface. We generally use PHP as an interface. We use the pg_exec function to create an indexed result set from a connection and statement. Then we iterate through each indexed row, calling pg_fetch_array so we can manipulate and display returned values. Hope this answers your question! -Ryan At 11:03 PM 7/28/01 +0200, Svenne Krap wrote: >Hi, > >is there any way to get the number of the tuple in the result set... > >in Oracle you can write > >select rownum, * from mytable; > >and then get (handmade example) : > >rownum | id | name >----------------------------- >1 | 27| John Dow >2 | 09 | Jane Dow >3 | 11 | Bugs Bunny --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
On Sat, 28 Jul 2001 22:05:55 +0000 (UTC), ryan@paymentalliance.net (Ryan Mahoney) wrote: >I think you should look into the documentation for your particular >interface. We generally use PHP as an interface. We use the pg_exec >function to create an indexed result set from a connection and >statement. Then we iterate through each indexed row, calling >pg_fetch_array so we can manipulate and display returned values. > >Hope this answers your question! Well not exactly. I thought of the possibility to do something like select rownum as artistplacement, s.* from (select rownum as techplacement, * from ranking order by technical_points) order by s.artiste_points In that way you could pull out two rankings at once. Infact, I really like the rownum for at lot of things, eventhough I am fully aware of the possibilities to do without (I have worked PHP for more than two years now, professionally - state of the art solutions including a lot of database connectivity, shared memory, semaphores, tcp/ip connections and so on (and quite often manipulating litteraly thousands of tuples inside PHP) on top notch machinery. So I wasn't really one of those newbie "how do I order the results after the primary key", it was a question asking for a very specific feature enableling me to do a lot of interesting stuff directly in the database-backend otherwise quite hard to accomplish (or maybe just with far too much work). Regards Svenne -- Mail usenet@krap.dk - svenne@krap.dk - PGP key id : 0xDF484022 ICQ: 5434480 - http://www.krap.dk - http://www.krap.net PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022
Svenne Krap <usenet@krap.dk> writes:
> I thought of the possibility to do something like
> select rownum as artistplacement, s.* from (select rownum as
> techplacement, * from ranking order by technical_points) order by
> s.artiste_points
This is an interesting example, but I do not believe that ROWNUM could
really be used that way. You are assuming that ROWNUM is computed after
the rows are sorted --- but in fact SQL requires the target expressions
of a SELECT to be computed before ORDER BY is applied. What would you
expect to happen with
SELECT rownum, * FROM table ORDER BY 1
Does anyone know what the actual semantics of Oracle's ROWNUM are?
regards, tom lane
On Mon, 30 Jul 2001 00:05:42 +0000 (UTC), tgl@sss.pgh.pa.us (Tom Lane) wrote: >Svenne Krap <usenet@krap.dk> writes: >> I thought of the possibility to do something like > >> select rownum as artistplacement, s.* from (select rownum as >> techplacement, * from ranking order by technical_points) order by >> s.artiste_points > >This is an interesting example, but I do not believe that ROWNUM could >really be used that way. You are assuming that ROWNUM is computed after >the rows are sorted --- but in fact SQL requires the target expressions >of a SELECT to be computed before ORDER BY is applied. What would you >expect to happen with > SELECT rownum, * FROM table ORDER BY 1 > >Does anyone know what the actual semantics of Oracle's ROWNUM are? Well, I'm quite positive, that Oracle calculates ROWNUM after having ordered the set. As I'm not sure, ROWNUM is counted as part of the resultset (until it is selected of a second select) In oracle btw. you use it for a construct like the following (not having LIMIT) select s.* from (select rownum, t.* from (select rownum,* from table where expressions order by sortcolumn) t where t.rownum < max_row) s where rownum > min_row And btw what actually happen to the data of a query if you do "order by 1" ??? Svenne -- Mail usenet@krap.dk - svenne@krap.dk - PGP key id : 0xDF484022 ICQ: 5434480 - http://www.krap.dk - http://www.krap.net PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022
Svenne Krap wrote: > > On Mon, 30 Jul 2001 00:05:42 +0000 (UTC), tgl@sss.pgh.pa.us (Tom Lane) > wrote: > > >Svenne Krap <usenet@krap.dk> writes: > >> I thought of the possibility to do something like > > > >> select rownum as artistplacement, s.* from (select rownum as > >> techplacement, * from ranking order by technical_points) order by > >> s.artiste_points > > > >This is an interesting example, but I do not believe that ROWNUM could > >really be used that way. You are assuming that ROWNUM is computed after > >the rows are sorted --- but in fact SQL requires the target expressions > >of a SELECT to be computed before ORDER BY is applied. What would you > >expect to happen with > > SELECT rownum, * FROM table ORDER BY 1 > > > >Does anyone know what the actual semantics of Oracle's ROWNUM are? > > Well, I'm quite positive, that Oracle calculates ROWNUM after having > ordered the set. As I'm not sure, ROWNUM is counted as part of the > resultset (until it is selected of a second select) > Isn't it a little different ? Oracle doc says. If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query": SELECT * FROM (SELECT empno FROM emp ORDER BY empno) WHERE ROWNUM < 11; regards, Hiroshi Inoue
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Oracle doc says.
> If you embed the ORDER BY clause in a subquery and place the ROWNUM
> condition in the top-level query, you can force the ROWNUM condition
> to be applied after the ordering of the rows. For example, the
> following query returns the 10 smallest employee numbers. This
> is sometimes referred to as a "top-N query":
> SELECT * FROM
> (SELECT empno FROM emp ORDER BY empno)
> WHERE ROWNUM < 11;
This thing gets more poorly-defined every time I hear about it!?
Based on what's been said so far, ROWNUM in a WHERE clause means
something completely different from ROWNUM in the SELECT target list:
it seems they mean input row count vs output row count, respectively.
If I do
SELECT rownum, * FROM foo WHERE rownum > 10 and rownum < 20;
will the output rows be numbered 1 to 9, or 11 to 19? If I add a
condition, say "AND field1 < 100", to the WHERE clause, does the rownum
count include the rows rejected by the additional clause, or not?
And how do you justify any of these behaviors in a coherent fashion?
Dare I ask how it behaves in the presence of GROUP BY, HAVING,
aggregates, DISTINCT, UNION, ... ?
regards, tom lane
Tom Lane wrote:
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
>
>>Oracle doc says.
>>
>
>>If you embed the ORDER BY clause in a subquery and place the ROWNUM
>>condition in the top-level query, you can force the ROWNUM condition
>>to be applied after the ordering of the rows. For example, the
>>following query returns the 10 smallest employee numbers. This
>>is sometimes referred to as a "top-N query":
>>
>
>>SELECT * FROM
>> (SELECT empno FROM emp ORDER BY empno)
>> WHERE ROWNUM < 11;
>>
>
> This thing gets more poorly-defined every time I hear about it!?
>
> Based on what's been said so far, ROWNUM in a WHERE clause means
> something completely different from ROWNUM in the SELECT target list:
> it seems they mean input row count vs output row count, respectively.
> If I do
> SELECT rownum, * FROM foo WHERE rownum > 10 and rownum < 20;
> will the output rows be numbered 1 to 9, or 11 to 19? If I add a
> condition, say "AND field1 < 100", to the WHERE clause, does the rownum
> count include the rows rejected by the additional clause, or not?
> And how do you justify any of these behaviors in a coherent fashion?
>
> Dare I ask how it behaves in the presence of GROUP BY, HAVING,
> aggregates, DISTINCT, UNION, ... ?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
here are the results you ask for (from Oracle8i) :
first : select rownum,* make a parse error :-)
SQL> select rownum,* from dba_tables where rownum > 10 and rownum < 20
*
ERROR at line 1:
ORA-00936: missing expression
And :
SQL> select rownum,table_name from dba_tables where rownum between 10
and 20;
no rows selected
SQL> select rownum,table_name from dba_tables where rownum > 10 and
rownum < 20;
no rows selected
SQL> select rownum,table_name from dba_tables where rownum > 10;
no rows selected
SQL> select rownum,table_name from dba_tables where rownum < 20;
ROWNUM TABLE_NAME
---------- ------------------------------
1 IND$
2 FILE$
3 UNDO$
4 CLU$
5 BOOTSTRAP$
6 ICOL$
7 FET$
8 CDEF$
9 CON$
10 UET$
11 TAB$
ROWNUM TABLE_NAME
---------- ------------------------------
12 OBJ$
13 PROXY$
14 COL$
15 USER$
16 TS$
17 CCOL$
18 SEG$
19 UGROUP$
19 rows selected.
SQL> select distinct rownum, table_name from dba_tables where rownum < 20;
Same result as above (19 rows)
SQL> select sum(rownum), tablespace_name from dba_tables where rownum
< 20 group by tablespace_name;
SUM(ROWNUM) TABLESPACE_NAME
----------- ------------------------------
190 SYSTEM
Don't have time for testing more ...
Hope this will help Postgresql development :-)
--
Nicolas Ronayette +33 (0)6 74 93 67 85
Alphacsp - +33 (0)1 41 37 75 75
--
Nicolas Ronayette <nronayette@alphacsp.com> writes:
> SQL> select rownum,table_name from dba_tables where rownum > 10;
> no rows selected
> SQL> select rownum,table_name from dba_tables where rownum < 20;
> ROWNUM TABLE_NAME
> ---------- ------------------------------
> 1 IND$
> 2 FILE$
> 3 UNDO$
> 4 CLU$
> 5 BOOTSTRAP$
> 6 ICOL$
> 7 FET$
> 8 CDEF$
> 9 CON$
> 10 UET$
> 11 TAB$
> ROWNUM TABLE_NAME
> ---------- ------------------------------
> 12 OBJ$
> 13 PROXY$
> 14 COL$
> 15 USER$
> 16 TS$
> 17 CCOL$
> 18 SEG$
> 19 UGROUP$
> 19 rows selected.
(Jaw drops...) That's just too bizarre for words.
I don't think I wanna have anything to do with this construct ...
regards, tom lane
> If I do > SELECT rownum, * FROM foo WHERE rownum > 10 and rownum < 20; > will the output rows be numbered 1 to 9, or 11 to 19? Such a select will never return any rows. Essentially rownum in a where predicate can only be used in the forms: rownum = 1; rownum < n; rownum <= n. Anything else will return no rows. For example rownum = 2 will return no rows because the first row returned by the query has by definition a rownum of 1, but the where predicate prevents this row from being returned, thus it can never get to a rownum value of 2 to satisfy the where predicate. In Oracle 8.1 they began allowing order by in the from clause to support the top-n type selects. Thus begining in 8.1 a query of the form "select * from (select foo from bar order by foo) where rownum < 10" became possible. Before 8.1 it wasn't legal to have an order by in this position, meaning you couldn't get a top-n result that was ordered. thanks, --Barry Tom Lane wrote: > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > >>Oracle doc says. >> > >>If you embed the ORDER BY clause in a subquery and place the ROWNUM >>condition in the top-level query, you can force the ROWNUM condition >>to be applied after the ordering of the rows. For example, the >>following query returns the 10 smallest employee numbers. This >>is sometimes referred to as a "top-N query": >> > >>SELECT * FROM >> (SELECT empno FROM emp ORDER BY empno) >> WHERE ROWNUM < 11; >> > > This thing gets more poorly-defined every time I hear about it!? > > Based on what's been said so far, ROWNUM in a WHERE clause means > something completely different from ROWNUM in the SELECT target list: > it seems they mean input row count vs output row count, respectively. > If I do > SELECT rownum, * FROM foo WHERE rownum > 10 and rownum < 20; > will the output rows be numbered 1 to 9, or 11 to 19? If I add a > condition, say "AND field1 < 100", to the WHERE clause, does the rownum > count include the rows rejected by the additional clause, or not? > And how do you justify any of these behaviors in a coherent fashion? > > Dare I ask how it behaves in the presence of GROUP BY, HAVING, > aggregates, DISTINCT, UNION, ... ? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
Barry Lind <barry@xythos.com> writes:
> ... For example rownum = 2 will return
> no rows because the first row returned by the query has by definition a
> rownum of 1, but the where predicate prevents this row from being
> returned, thus it can never get to a rownum value of 2 to satisfy the
> where predicate.
So in other words, a construct accessible in the WHERE clause is defined
in terms of what happens far downstream of WHERE. This cannot possibly
have sane behavior. I won't even ask about join queries...
AFAICT, LIMIT/OFFSET do the same job in a much more logical fashion.
Let's stick with those, and not try to copy the more brain-dead aspects
of Oracle.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > Oracle doc says. > > > If you embed the ORDER BY clause in a subquery and place the ROWNUM > > condition in the top-level query, you can force the ROWNUM condition > > to be applied after the ordering of the rows. For example, the > > following query returns the 10 smallest employee numbers. This > > is sometimes referred to as a "top-N query": > > > SELECT * FROM > > (SELECT empno FROM emp ORDER BY empno) > > WHERE ROWNUM < 11; > > This thing gets more poorly-defined every time I hear about it!? > > Based on what's been said so far, ROWNUM in a WHERE clause means > something completely different from ROWNUM in the SELECT target list: > it seems they mean input row count vs output row count, respectively. They mean output row count AFAIK. > If I do > SELECT rownum, * FROM foo WHERE rownum > 10 and rownum < 20; > will the output rows be numbered 1 to 9, or 11 to 19? No rows are returned because rownum 2 doesn't exist without rownum 1 and so on. > If I add a > condition, say "AND field1 < 100", to the WHERE clause, does the rownum > count include the rows rejected by the additional clause, or not? Not. > And how do you justify any of these behaviors in a coherent fashion? > > Dare I ask how it behaves in the presence of GROUP BY, HAVING, > aggregates, DISTINCT, UNION, ... ? > I don't know the details about it unfortunately. regards, Hiroshi Inoue