Re: Re: "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(at)krap(dot)dk - svenne(at)krap(dot)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

On Sat, 28 Jul 2001 22:05:55 +0000 (UTC), ryan(at)paymentalliance(dot)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(at)krap(dot)dk - svenne(at)krap(dot)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(at)krap(dot)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(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane)
wrote:

>Svenne Krap <usenet(at)krap(dot)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(at)krap(dot)dk - svenne(at)krap(dot)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(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane)
> wrote:
>
> >Svenne Krap <usenet(at)krap(dot)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(at)tpf(dot)co(dot)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(at)tpf(dot)co(dot)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(at)alphacsp(dot)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(at)tpf(dot)co(dot)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(at)xythos(dot)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(at)sss(dot)pgh(dot)pa(dot)us]
>
> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)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