Natural order
David Schwartz, December 21, 2004 - 8:18 am UTC
>>There is no such "concept" of "order of insertion"
I thought there was a concept of "natural order", which would be the same as "order of insertion".
Apparently I was just lucky all this time.
Thanks
Rows to Column Order
Muhammad Waseem Haroon, April 05, 2005 - 2:34 pm UTC
I have a table named Department
SQL> SELECT *
2 FROM Department
3 ORDER BY Dept_Name;
Dept_Code Dept_Name
--------- ----------------------
1 ADMIN & FUNCTIONS
2 FINANCE & AUDIT
3 HUMAN RESOURCE
4 INFORMATION TECHNOLOGY
5 OPERATION
6 QMS
7 RO
8 SECRETARIAT
9 TECHNICAL
10 UTILITIES
11 WEAVING
I want to Devide "n" Rows into "n" Columns, But the "Order By" Should be on Every Column Like:
Dept_Name_Col_1 Dept_Name_Col_2 Dept_Name_Col_3
---------------------- ---------------------- ----------------------
ADMIN & FUNCTIONS OPERATION TECHNICAL
FINANCE & AUDIT QMS UTILITIES
HUMAN RESOURCE RO WEAVING
INFORMATION TECHNOLOGY SECRETARIAT
1) is that possible from single SELECT Statement ?
1) is there any Oracle Buil-in Procedure or Function to do this ?
3) How Can i do that ?
Thank in Advance
Muhammad Waseem Haroon
mwaseem_haroon@yahoo.com
April 05, 2005 - 6:49 pm UTC
no create table?
no insert intos?
logically handle
raaghid, April 06, 2005 - 1:23 am UTC
"To get the rows retrieved in the way it has been inserted",
only one way out
-- Have a new field with number increment (using sequence)
-- While doing select, use the field as 1st column in order by.
April 06, 2005 - 6:41 am UTC
and hope that is the way you want them.
session 1 session 2
insert seq=1
insert seq=2
commit;
commit;
which row was "first". I say session 2's row is older than session 1's because of commit order...
but yes, you need something to order by to get rows out in any order.
Date / Time stamp
Ian, April 06, 2005 - 7:52 am UTC
Hi Tom,
Would I be correct in saying that a date/time stamp would be the way to go here?
A column with a default value of SYSDATE would work right?
Anything evil about that statement (from a database point of view, not fundamental Christianity type of evil :D)
Cheers,
Ian
April 06, 2005 - 9:16 am UTC
it would have the same effect as a sequence.
but you can use a timestamp sure, it is as good as anything else (but a sequence would be unique, timestamps will not be)
Returning rows in the order they were inserted
veera, April 06, 2005 - 9:14 am UTC
How about using
ORDER BY ROWID
if oracle stores file no# and block# in ascending order.
April 06, 2005 - 1:17 pm UTC
it doesn't.
if it did, tables would only grow, they would never reuse space....
rows have no reliable order in a result set without an order by.
period. fact of SQL.
rownum on to-n query
daxu, April 06, 2005 - 9:36 am UTC
April 06, 2005 - 1:24 pm UTC
that'll not get the top-n
a top-n looks like this:
select *
from ( YOUR_QUERY_HERE_WITH_ORDER_BY_AND_EVERYTHING )
where rownum <= :n;
that'll sort your query and return the first N rows.
Reply to Muhammad's question yesterday....
Mark J. Bobak, April 06, 2005 - 12:15 pm UTC
I thought it was interesting. Here's what I came up with:
create table department(dept_code number, dept_name varchar2(25);
set define off
insert into department values(1,'ADMIN & FUNCTIONS');
insert into department values(2,'FINANACE & AUDIT');
insert into department values(3,'HUMAN RESOURCE');
insert into department values(4,'INFORMATION TECHNOLOGY');
insert into department values(5,'OPERATION');
insert into department values(6,'QMS');
insert into department values(7,'RO');
insert into department values(8,'SECRETARIAT');
insert into department values(9,'TECHNICAL');
insert into department values(10,'UTILITIES');
insert into department values(11,'WEAVING');
commit;
SQL> l
1 select dept_name1, dept_name2, dept_name3 from(
2 select column_group,
3 dept_name dept_name1,
4 lead(dept_name,4) over(order by column_group) dept_name2,
5 lead(dept_name,8) over(order by column_group) dept_name3
6 from (select round((dept_code+1)/4) column_group, dept_name from department)
7 )
8* where column_group = 1
SQL> /
DEPT_NAME1 DEPT_NAME2 DEPT_NAME3
------------------------- ------------------------- -------------------------
ADMIN & FUNCTIONS OPERATION TECHNICAL
FINANACE & AUDIT QMS UTILITIES
HUMAN RESOURCE RO WEAVING
INFORMATION TECHNOLOGY SECRETARIAT
I think that ought to do it.
April 06, 2005 - 2:15 pm UTC
slightly different approach
ops$tkyte@ORA10G> select max(decode( col, 0, dept_name )) c1,
2 max(decode( col, 1, dept_name )) c2,
3 max(decode( col, 2, dept_name )) c3
4 from (
5 select dept_name, floor(rn/cnt-0.1) col, rn-(floor(rn/cnt-0.1)*cnt) n
6 from (
7 select dept_name, row_number() over (order by dept_name) rn,
ceil(count(*) over ()/3) cnt
8 from department
9 )
10 )
11 group by n
12 order by n;
C1 C2 C3
------------------------- ------------------------- -------------------------
ADMIN & FUNCTIONS OPERATION TECHNICAL
FINANACE & AUDIT QMS UTILITIES
HUMAN RESOURCE RO WEAVING
INFORMATION TECHNOLOGY SECRETARIAT
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec insert into department values ( 12, 'WEAVING 2' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> /
C1 C2 C3
------------------------- ------------------------- -------------------------
ADMIN & FUNCTIONS OPERATION TECHNICAL
FINANACE & AUDIT QMS UTILITIES
HUMAN RESOURCE RO WEAVING
INFORMATION TECHNOLOGY SECRETARIAT WEAVING 2
ops$tkyte@ORA10G> exec insert into department values ( 13, 'WEAVING 3' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> /
C1 C2 C3
------------------------- ------------------------- -------------------------
ADMIN & FUNCTIONS QMS WEAVING
FINANACE & AUDIT RO WEAVING 2
HUMAN RESOURCE SECRETARIAT WEAVING 3
INFORMATION TECHNOLOGY TECHNICAL
OPERATION UTILITIES
Even better.....
Mark J. Bobak, April 06, 2005 - 1:34 pm UTC
Actually, I made it more complex than necessary.
This will work:
select dept_name1, dept_name2, dept_name3 from(
select
dept_name dept_name1,
lead(dept_name,4) over(order by dept_code) dept_name2,
lead(dept_name,8) over(order by dept_code) dept_name3
from department
)
where rownum <=4;
April 06, 2005 - 2:18 pm UTC
but relies on fixed numbers of rows.... eg: after my extra inserts above:
ops$tkyte@ORA10G> select dept_name1, dept_name2, dept_name3 from(
2 select
3 dept_name dept_name1,
4 lead(dept_name,4) over(order by dept_code) dept_name2,
5 lead(dept_name,8) over(order by dept_code) dept_name3
6 from department
7 )
8 where rownum <=4;
DEPT_NAME1 DEPT_NAME2 DEPT_NAME3
------------------------- ------------------------- -------------------------
ADMIN & FUNCTIONS OPERATION TECHNICAL
FINANACE & AUDIT QMS UTILITIES
HUMAN RESOURCE RO WEAVING
INFORMATION TECHNOLOGY SECRETARIAT WEAVING 2
Ok,how about this?? ;-)
Mark J. Bobak, April 06, 2005 - 4:10 pm UTC
with subq as (select round(count(*)/3)+1 from department)
select dept_name1, dept_name2, dept_name3
from(
select
dept_name dept_name1,
lead(dept_name,(select * from subq)) over(order by dept_code) dept_name2,
lead(dept_name,(select * from subq)*2) over(order by dept_code) dept_name3
from department)
where rownum <=(select * from subq);
Ok, ok, before you even go there, I concede that
your way is better, and more efficient. (I got 2
recursive calls, 8 db block gets and 25 consistent gets.
Your query does 0 recursive, 0 db block gets and only
7 consistent gets.)
So, now I go back and re-read your query about 137 times,
and then pick it apart layer by layer, until I totally
understand how it works.
<Mark begins meditating, "be one with the code....
be one with the code...."> ;-)
Meditation summary.....
Mark J. Bobak, April 06, 2005 - 4:22 pm UTC
Ok, I think I understand it now.....
The innermost query simply computes how many rows there
will be, given the number of rows in the table, and the
number of columns (which is 3 in this case). Then,
the next layer computes which column, and which row in
that column, the item belongs in. Finally, the outermost
utilizes max and decode to simply print the appropriate
dept_name at the appropriate place.
The beauty is, at each level, where the earlier computation
is being done, the result is carried along w/ the dept_name
data, avoiding having to scan the table more than once.
So, I broke it down and I (think I) understand it, but,
it will take a whole new level of enlightenment to be
able to recognize this type of problem next time around
and implement this solution.
Sigh, time for more meditation, I suppose....
Om Mane Padme Hum....Om Mane Padme Hum....
some doubt on the format ...
Gabe, April 06, 2005 - 5:57 pm UTC
Not clear what the output should be when the table has 3*n+1 rows (where n is a natural number) ...
flip@FLOP> select * from department order by 1;
DEPT_CODE DEPT_NAME
---------- -------------------------
1 ADMIN & FUNCTIONS
2 FINANACE & AUDIT
3 HUMAN RESOURCE
4 INFORMATION TECHNOLOGY
flip@FLOP> select max(decode( col, 0, dept_name )) c1,
2 max(decode( col, 1, dept_name )) c2,
3 max(decode( col, 2, dept_name )) c3
4 from (
5 select dept_name, floor(rn/cnt-0.1) col, rn-(floor(rn/cnt-0.1)*cnt) n
6 from (
7 select dept_name, row_number() over (order by dept_name) rn,
8 ceil(count(*) over ()/3) cnt
9 from department
10 )
11 )
12 group by n
13 order by n;
C1 C2 C3
------------------------- ------------------------- -------------------------
ADMIN & FUNCTIONS HUMAN RESOURCE
FINANACE & AUDIT INFORMATION TECHNOLOGY
or ...
flip@FLOP> /
C1 C2 C3
------------------------- ------------------------- -------------------------
ADMIN & FUNCTIONS HUMAN RESOURCE INFORMATION TECHNOLOGY
FINANACE & AUDIT
Just curious.
April 06, 2005 - 7:10 pm UTC
given the way they asked to "sort the snaked columns", I think format 1 is more correct.
(and since that is what my query does :)
To Mark
Tanmoy, April 07, 2005 - 3:56 am UTC
I dont know from where you have learned the meditation mantra
Om Mane Padme Hum....Om Mane Padme Hum....
Here is another one for you
Om shanti ..Om shanti .. Om shanti
chill out .. :)
Thanks Tom, But...
Muhammad Waseem Haroon, April 07, 2005 - 9:20 am UTC
Thank you very Much TOM,
You have Realy Resolved My Problem by :
select max(decode( col, 0, emp_name )) c1,
max(decode( col, 1, emp_name)) c2,
max(decode( col, 2, emp_name)) c3,
max(decode( col, 3, emp_name)) c4
from (
select emp_name , floor(rn/cnt-0.1) col, rn-(floor(rn/cnt-0.1)*cnt) n
from (
select emp_name, row_number() over (order by emp_name) rn,
ceil(count(*) over ()/4) cnt
from employee
)
)
group by n
order by n ;
But Can I Create an Object(Procedure or Function) to use it anywhere by using aurguments of " Object(schema.table_name, n_rown_in_1_Col) "?
Thanks Again
Muhammad Waseem Haroon
mwaseem_haroon@yahoo.com
April 07, 2005 - 11:07 am UTC
dynamic sql.
you need to input the column name, the table name.
open ref_cursor for '
select max(decode( col, 0, data)) c1,
max(decode( col, 1, data)) c2,
max(decode( col, 2, data)) c3,
max(decode( col, 3, data)) c4
from (
select data,
floor(rn/cnt-0.1) col, rn-(floor(rn/cnt-0.1)*cnt) n
from (
select ' || p_cname || ' data,
row_number() over (order by ' || p_cname || ') rn,
ceil(count(*) over ()/4) cnt
from ' || p_tname || '
)
)
group by n
order by n';