[Q] Sorting a column

On Aug 7, 3:48 am, digory wrote:
> I just tried the following
>
> UPDATE T
> SET pos = DENSE_RANK () OVER (ORDER BY name)
>
> Unfortunately, this does not work because Oracle does not allow
> analytic functions in the SET clause.

This does:

declare
cursor get_rank is]
select name, dense_rank() over (order by name) dr
from t;
begin
for gr in get_rank loop
update t
set pos = gr.dr
where name = gr.name;
end loop;

commit;
end;
/

Here's proof:

SQL> select * From t;

NAME POS
-------------------- ----------
SMITH
SMYTHE
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

NAME POS
-------------------- ----------
ADAMS
JAMES
FORD
MILLER

15 rows selected.

SQL> declare
2 cursor get_rank is
3 select name, dense_rank() over (order by name) dr
4 from t;
5 begin
6 for gr in get_rank loop
7 update t
8 set pos = gr.dr
9 where name = gr.name;
10 end loop;
11
12 commit;
13 end;
14 /

PL/SQL procedure successfully completed.

SQL> select * from t;

NAME POS
-------------------- ----------
SMITH 12
SMYTHE 13
ALLEN 2
WARD 15
JONES 7
MARTIN 9
BLAKE 3
CLARK 4
SCOTT 11
KING 8
TURNER 14

NAME POS
-------------------- ----------
ADAMS 1
JAMES 6
FORD 5
MILLER 10

15 rows selected.

SQL>


David Fitzjarrell
 >> Stay informed about: [Q] Sorting a column 

digory wrote:
> I just tried the following

> UPDATE T
> SET pos = DENSE_RANK () OVER (ORDER BY name)

> Unfortunately, this does not work because Oracle does not allow
> analytic functions in the SET clause.

update t
set pos =
(select new_id
from (select rowid, dense_rank() over (order by name) new_id
from t) t2
where t2.rowid = t.rowid);

--
_______________________________________________________________________
Dan Blum tool RemoveThis @panix.com
"I wouldn't have believed it myself if I hadn't just made it up."
 >> Stay informed about: [Q] Sorting a column 

digory wrote:
> Hi
>
> I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
> want to write an UPDATE query, which updates the column POS such that
> its values correspond to the alphabetical order of NAME.
>
> UPDATE
> T t1
> SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
> NAME)
>
> That does not work, because the WHERE clause returns a single row,
> which will always have a ROWID of 1.
>
> How do I do this? (It's possible with a PROCEDURE, of course, but I
> want to avoid them.)

NAME is a reserved word ... rename your column
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan.DeleteThis@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 >> Stay informed about: [Q] Sorting a column 

On Aug 8, 1:58 am, "Shakespeare" wrote:
> "digory" schreef in berichtnews:1210a149-d43e-48a8-bbd7-9a688c96fa88@y38g2000hsy.googlegroups.com...
>
>
>
>
>
> > Hi
>
> > I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
> > want to write an UPDATE query, which updates the column POS such that
> > its values correspond to the alphabetical order of NAME.
>
> > UPDATE
> >   T t1
> > SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
> > NAME)
>
> > That does not work, because the WHERE clause returns a single row,
> > which will always have a ROWID of 1.
>
> > How do I do this? (It's possible with a PROCEDURE, of course, but I
> > want to avoid them.)
>
> ROWID? Don't you mean rownum?
> I don't think tables will ever have rows with the same rowid (except by
> coincidence)
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Look again, it's the same table queried twice. The rowids WILL match
up.


David Fitzjarrell
 >> Stay informed about: [Q] Sorting a column 

"digory" schreef in bericht

> Hi
>
> I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
> want to write an UPDATE query, which updates the column POS such that
> its values correspond to the alphabetical order of NAME.
>
> UPDATE
> T t1
> SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
> NAME)
>
> That does not work, because the WHERE clause returns a single row,
> which will always have a ROWID of 1.
>
> How do I do this? (It's possible with a PROCEDURE, of course, but I
> want to avoid them.)

ROWID? Don't you mean rownum?
I don't think tables will ever have rows with the same rowid (except by
coincidence)

Shakespeare
 >> Stay informed about: [Q] Sorting a column 

Shakespeare wrote:
> "digory" schreef in bericht
>
>> Hi
>>
>> I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
>> want to write an UPDATE query, which updates the column POS such that
>> its values correspond to the alphabetical order of NAME.
>>
>> UPDATE
>> T t1
>> SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
>> NAME)
>>
>> That does not work, because the WHERE clause returns a single row,
>> which will always have a ROWID of 1.
>>
>> How do I do this? (It's possible with a PROCEDURE, of course, but I
>> want to avoid them.)
>
> ROWID? Don't you mean rownum?
> I don't think tables will ever have rows with the same rowid (except by
> coincidence)
>
> Shakespeare

Not even by coincidence ... it is a technical impossibility.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan RemoveThis @x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 >> Stay informed about: [Q] Sorting a column 

Shakespeare wrote:

> "digory" schreef in bericht
>
> > Hi
> >
> > I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
> > want to write an UPDATE query, which updates the column POS such that
> > its values correspond to the alphabetical order of NAME.
> >
> > UPDATE
> > T t1
> > SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
> > NAME)
> >
> > That does not work, because the WHERE clause returns a single row,
> > which will always have a ROWID of 1.
> >
> > How do I do this? (It's possible with a PROCEDURE, of course, but I
> > want to avoid them.)

> ROWID? Don't you mean rownum?
> I don't think tables will ever have rows with the same rowid (except by
> coincidence)

Only if they're in a cluster - rows that have the same cluster key have
the same ROWID (or so it says in the docs, I haven't tried it).

--
_______________________________________________________________________
Dan Blum tool DeleteThis @panix.com
"I wouldn't have believed it myself if I hadn't just made it up."
 >> Stay informed about: [Q] Sorting a column 

Dan Blum wrote:

> Only if they're in a cluster - rows that have the same cluster key have
> the same ROWID (or so it says in the docs, I haven't tried it).

Not even then.

SQL> CREATE CLUSTER hcl_srvr_id (
2 si_clustercol NUMBER(10))
3 PCTFREE 0
4 TABLESPACE uwdata
5 HASHKEYS 141
6 ROWDEPENDENCIES;

Cluster created.

SQL> CREATE TABLE cservers (
2 srvr_id NUMBER(10),
3 network_id NUMBER(10),
4 status VARCHAR2(1),
5 latitude FLOAT(20),
6 longitude FLOAT(20),
7 netaddress VARCHAR2(15))
8 CLUSTER hcl_srvr_id (srvr_id);

Table created.

SQL> CREATE TABLE cserv_inst (
2 siid NUMBER(10),
3 si_status VARCHAR2(15),
4 type VARCHAR2(5),
5 installstatus VARCHAR2(1),
6 location_code NUMBER(10),
7 custacct_id VARCHAR2(10),
8 srvr_id NUMBER(10),
9 ws_id NUMBER(10))
10 CLUSTER hcl_srvr_id (srvr_id);

Table created.

SQL> insert into cservers
2 select * from servers;

141 rows created.

SQL> insert into cserv_inst
2 select * from serv_inst;

999 rows created.

SQL> select rowid, count(*)
2 from cservers
3 group by rowid
4 having count(*) > 1;

no rows selected

SQL> select rowid, count(*)
2 from cserv_inst
3 group by rowid
4 having count(*) > 1;

no rows selected

SQL> CREATE CLUSTER sc_srvr_id (
2 srvr_id NUMBER(10))
3 SIZE 1024;

Cluster created.

SQL> CREATE INDEX idx_sc_srvr_id ON CLUSTER sc_srvr_id;

Index created.

SQL> drop table cservers purge;

Table dropped.

SQL> drop table cserv_inst purge;

Table dropped.

SQL> CREATE TABLE cservers (
2 srvr_id NUMBER(10),
3 network_id NUMBER(10),
4 status VARCHAR2(1),
5 latitude FLOAT(20),
6 longitude FLOAT(20),
7 netaddress VARCHAR2(15))
8 CLUSTER sc_srvr_id (srvr_id);

Table created.

SQL> CREATE TABLE cserv_inst (
2 siid NUMBER(10),
3 si_status VARCHAR2(15),
4 type VARCHAR2(5),
5 installstatus VARCHAR2(1),
6 location_code NUMBER(10),
7 custacct_id VARCHAR2(10),
8 srvr_id NUMBER(10),
9 ws_id NUMBER(10))
10 CLUSTER sc_srvr_id (srvr_id);

Table created.

SQL> insert into cservers
2 select * from servers;

141 rows created.

SQL> insert into cserv_inst
2 select * from serv_inst;

999 rows created.

SQL> select rowid, count(*)
2 from servers
3 group by rowid
4 having count(*) > 1;

no rows selected

SQL> select rowid, count(*)
2 from serv_inst
3 group by rowid
4 having count(*) > 1;

no rows selected

SQL>

You should have tried it before making an assumption and
claiming it was true.

The datafile will be identical.
The block will be identical.
But the row itself? Not likely.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan.TakeThisOut@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 >> Stay informed about: [Q] Sorting a column 

"fitzjarrell@cox.net" schreef in bericht

On Aug 8, 1:58 am, "Shakespeare" wrote:
> "digory" schreef in
> berichtnews:1210a149-d43e-48a8-bbd7-9a688c96fa88@y38g2000hsy.googlegroups.com...
>
>
>
>
>
> > Hi
>
> > I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
> > want to write an UPDATE query, which updates the column POS such that
> > its values correspond to the alphabetical order of NAME.
>
> > UPDATE
> > T t1
> > SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
> > NAME)
>
> > That does not work, because the WHERE clause returns a single row,
> > which will always have a ROWID of 1.
>
> > How do I do this? (It's possible with a PROCEDURE, of course, but I
> > want to avoid them.)
>
> ROWID? Don't you mean rownum?
> I don't think tables will ever have rows with the same rowid (except by
> coincidence)
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Look again, it's the same table queried twice. The rowids WILL match
up.


=============================

Right. OOPS

Shakespeare

David Fitzjarrell
 >> Stay informed about: [Q] Sorting a column 

DA Morgan wrote:

> You should have tried it before making an assumption and
> claiming it was true.

> The datafile will be identical.
> The block will be identical.
> But the row itself? Not likely.

Your argument is with the Oracle docs, not me. The 10.2 docs claim it can
happen, but they don't provide details.

--
_______________________________________________________________________
Dan Blum tool.RemoveThis@panix.com
"I wouldn't have believed it myself if I hadn't just made it up."
 >> Stay informed about: [Q] Sorting a column 

DA Morgan schrieb:
> Dan Blum wrote:
>
>> Only if they're in a cluster - rows that have the same cluster key have
>> the same ROWID (or so it says in the docs, I haven't tried it).
>
> Not even then.
>
> SQL> CREATE CLUSTER hcl_srvr_id (
> 2 si_clustercol NUMBER(10))
> 3 PCTFREE 0
> 4 TABLESPACE uwdata
> 5 HASHKEYS 141
> 6 ROWDEPENDENCIES;
>
> Cluster created.
>
> SQL> CREATE TABLE cservers (
> 2 srvr_id NUMBER(10),
> 3 network_id NUMBER(10),
> 4 status VARCHAR2(1),
> 5 latitude FLOAT(20),
> 6 longitude FLOAT(20),
> 7 netaddress VARCHAR2(15))
> 8 CLUSTER hcl_srvr_id (srvr_id);
>
> Table created.
>
> SQL> CREATE TABLE cserv_inst (
> 2 siid NUMBER(10),
> 3 si_status VARCHAR2(15),
> 4 type VARCHAR2(5),
> 5 installstatus VARCHAR2(1),
> 6 location_code NUMBER(10),
> 7 custacct_id VARCHAR2(10),
> 8 srvr_id NUMBER(10),
> 9 ws_id NUMBER(10))
> 10 CLUSTER hcl_srvr_id (srvr_id);
>
> Table created.
>
> SQL> insert into cservers
> 2 select * from servers;
>
> 141 rows created.
>
> SQL> insert into cserv_inst
> 2 select * from serv_inst;
>
> 999 rows created.
>
> SQL> select rowid, count(*)
> 2 from cservers
> 3 group by rowid
> 4 having count(*) > 1;
>
> no rows selected
>
> SQL> select rowid, count(*)
> 2 from cserv_inst
> 3 group by rowid
> 4 having count(*) > 1;
>
> no rows selected
>
> SQL> CREATE CLUSTER sc_srvr_id (
> 2 srvr_id NUMBER(10))
> 3 SIZE 1024;
>
> Cluster created.
>
> SQL> CREATE INDEX idx_sc_srvr_id ON CLUSTER sc_srvr_id;
>
> Index created.
>
> SQL> drop table cservers purge;
>
> Table dropped.
>
> SQL> drop table cserv_inst purge;
>
> Table dropped.
>
> SQL> CREATE TABLE cservers (
> 2 srvr_id NUMBER(10),
> 3 network_id NUMBER(10),
> 4 status VARCHAR2(1),
> 5 latitude FLOAT(20),
> 6 longitude FLOAT(20),
> 7 netaddress VARCHAR2(15))
> 8 CLUSTER sc_srvr_id (srvr_id);
>
> Table created.
>
> SQL> CREATE TABLE cserv_inst (
> 2 siid NUMBER(10),
> 3 si_status VARCHAR2(15),
> 4 type VARCHAR2(5),
> 5 installstatus VARCHAR2(1),
> 6 location_code NUMBER(10),
> 7 custacct_id VARCHAR2(10),
> 8 srvr_id NUMBER(10),
> 9 ws_id NUMBER(10))
> 10 CLUSTER sc_srvr_id (srvr_id);
>
> Table created.
>
> SQL> insert into cservers
> 2 select * from servers;
>
> 141 rows created.
>
> SQL> insert into cserv_inst
> 2 select * from serv_inst;
>
> 999 rows created.
>
> SQL> select rowid, count(*)
> 2 from servers
> 3 group by rowid
> 4 having count(*) > 1;
>
> no rows selected
>
> SQL> select rowid, count(*)
> 2 from serv_inst
> 3 group by rowid
> 4 having count(*) > 1;
>
> no rows selected
>
> SQL>
>
> You should have tried it before making an assumption and
> claiming it was true.
>
> The datafile will be identical.
> The block will be identical.
> But the row itself? Not likely.

All rows from the same table within the same cluster key will still have
different slot numbers, hence different rowid's. But it is of course
possible for different tables within same cluster key to have same slot
numbers, so i think, Dan meant something like this

SQL> create cluster emp_dept(
2 deptno number(2)
3 )
4 ;

Cluster created.

SQL> create index emp_dept_idx on cluster emp_dept
2 ;

Index created.

SQL> create table emp_c
2 cluster emp_dept(deptno)
3 as select * from emp
4 ;

Table created.

SQL> create table dept_c
2 cluster emp_dept(deptno)
3 as select * from dept
4 ;

Table created.

SQL> select count(*) from (
2 select rowid from emp_c
3 intersect
4 select rowid from dept_c
5 )
6 ;

COUNT(*)
----------
3

SQL> select e.ename,e.deptno,d.dname
2 from emp_c e,dept_c d
3 where e.rowid=d.rowid
4 ;

ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
CLARK 10 ACCOUNTING


Best regards

Maxim
 >> Stay informed about: [Q] Sorting a column 

Dan Blum wrote:
> DA Morgan wrote:
>
>> You should have tried it before making an assumption and
>> claiming it was true.
>
>> The datafile will be identical.
>> The block will be identical.
>> But the row itself? Not likely.
>
> Your argument is with the Oracle docs, not me. The 10.2 docs claim it can
> happen, but they don't provide details.

Maxim demonstrates how.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan.RemoveThis@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 >> Stay informed about: [Q] Sorting a column 

"DA Morgan" schreef in bericht

> Shakespeare wrote:
>> "digory" schreef in bericht
>>
>>> Hi
>>>
>>> I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
>>> want to write an UPDATE query, which updates the column POS such that
>>> its values correspond to the alphabetical order of NAME.
>>>
>>> UPDATE
>>> T t1
>>> SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
>>> NAME)
>>>
>>> That does not work, because the WHERE clause returns a single row,
>>> which will always have a ROWID of 1.
>>>
>>> How do I do this? (It's possible with a PROCEDURE, of course, but I
>>> want to avoid them.)
>>
>> ROWID? Don't you mean rownum?
>> I don't think tables will ever have rows with the same rowid (except by
>> coincidence)
>>
>> Shakespeare
>
> Not even by coincidence ... it is a technical impossibility.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damorgan.TakeThisOut@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Just by curiousity (and too lazy to look in the manuals...): does Oracle
change ROWID's when a transportable tablespace is moved from one system to
an other?

Shakespeare
 >> Stay informed about: [Q] Sorting a column