OraFAQ Forum: SQL & PL/SQL » The number of a row inside a table (without ROWNUM)

Home » SQL & PL/SQL » SQL & PL/SQL » The number of a row inside a table (without ROWNUM)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

The number of a row inside a table (without ROWNUM) [message #8592]

Wed, 03 September 2003 03:17

Gerald
Messages: 54
Registered: January 2001

Member

Dudz,

i've done a script that give back the fields NULL from a table.
I want to format a report like that:

Name of the table N�ROW N�COL COLNAME
----------------- ----- ----- -------
Toto 2 2 Field1
2 4 Field4
2 8 Field8

8 1 Field1
8 5 Field5

But i can't have the number of the row.
With Rownum, i will have the number of this row fetched by a select so it will be N�1 (for ROW #2) and N�2 (for row #8). A guy which want to change the field by using a graphic tool like toad, will want to acces directly to the data and change it.
So he needs , the exact position.

i've used the ROwid to be sure to have a trace of the row.
and i've tried:

select count(*)
from CLO_TARIF
where rowid='AABWZAAAGAAAEkBAAA';

(where CLO_TARIF is the name of the TABLE, and AABWZ...AAA is the ROWid of a ROW with NULL Values)
it gives back : count(*) 1

I've changed with ROWNUM:
select rownum
from CLO_TARIF
where rowid='AABWZAAAGAAAEkBAAA';
it gives: rownum 1

So how can i do to have the exact number of this row in tha table (which is the number 8 - insertation moment) ? I need Number 8 and i always have 1
...
Take Care
Gerald

Report message to a moderator

Re: The number of a row inside a table (without ROWNUM) [message #8593 is a reply to message #8592]

Wed, 03 September 2003 03:55

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Position is relative...you need to sort. Oracle by default, and as part of the relational paradigm, doesn't store records in any particular order. ORDER BY and use RANK() or DENSE_RANK(). Look at this:

SQL> Select ename
  2      , dense_rank() over (order by sal desc) r
  3   From emp
  4  Order By r
  5  /

ENAME              R
---------- ---------
KING               1
SCOTT              2
FORD               2
JONES              3
BLAKE              4
CLARK              5
ALLEN              6
TURNER             7
MILLER             8
WARD               9
MARTIN             9
ADAMS             10
JAMES             11
SMITH             12

14 rows selected.

SQL> Select ename
  2       , r
  3    From ( Select ename
  4                , dense_rank() over (order by sal desc) r
  5             From emp
  6            Order By r
  7         )
  8   Where r = 8
  9  /

ENAME              R
---------- ---------
MILLER             8

SQL>

HTH,
MHE

Report message to a moderator

Re: The number of a row inside a table (without ROWNUM) [message #8599 is a reply to message #8593]

Wed, 03 September 2003 06:59

Gerald
Messages: 54
Registered: January 2001

Member

Thank MHE,

well as i don't know during the script which rank is therow i can't use the clause WHERE.
Anyway I find another solution to get the info without the Rank of the ROW ...
selecting * from the target table where any field is NULL where target.rowid = stored(rowid)
I spool that and i might have the good report

Thanks
see you
Gerald

Report message to a moderator

Re: The number of a row inside a table (without ROWNUM) [message #8611 is a reply to message #8599]

Thu, 04 September 2003 01:00

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I believe ROWID isn't such a good idea because you never know when it changes. ROWID refers to the physical storage of a certain record. One of the primary goals of a database is to separate the logical and functional storage from the physical storage. If it wouldn't, we might just as well use the filesystem instead of the database... . ROWIDs aren't static btw, in certain cases they might change.

There's no way in which you can uniquely identify a row but with ROWID? Then you have a poor design because you seem to miss a PRIMARY KEY which serves exactly this purpose (uniquely identify a row in a table).

MHE

Report message to a moderator

Previous Topic:	How to find UTL_FILE end of file
Next Topic:	Updating rows in a trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:08:32 CDT 2024