| Cut data into groups [message #383685] |
Thu, 29 January 2009 13:16  |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
Hi
I am using a query to extract information from my database and this information is saved into a file and later on this file is used as input data for another special program.
Now the problem is this that special program do not accept accept duplicate records on a particular field. So, as a solution, i want to cut my data into groups.
Let me try to explain with the help of common EMP table.
I have run the command and the output is
select ename, deptno
from emp
order by 2
ENAME DEPTNO
---------- ----------
CLARK 10
KING 10
MILLER 10
SMITH 20
ADAMS 20
FORD 20
SCOTT 20
JONES 20
ALLEN 30
BLAKE 30
MARTIN 30
JAMES 30
TURNER 30
WARD 30
14 rows selected
Suppose, above data is being used as input for my special program and it looks duplicate in deptno field only. When i load these 14 records through file, it loads only 3 records and rest 11 are deleted.
The only possible solution for me is to cut these data into many groups as follow:
| Quote: |
Group-1
CLARK 10
SMITH 20
ALLEN 30
Group-2
KING 10
ADAMS 20
BLAKE 30
Group-3
MILLER 10
FORD 20
MARTIN 30
Group-4
SCOTT 20
JAMES 30
Group-5
JONES 20
TURNER 30
Group-6
WARD 30
|
The order that one particular record must be in group-1 is not necessary.i.e i mean CLARK and KING can exchange their "seats".
I thought this can be done with ROWNUM but i don't have experience on it.
Waiting for reply.
-Dude
|
|
|
|
|
|
|
|
| Re: Cut data into groups [message #383726 is a reply to message #383685] |
Thu, 29 January 2009 20:28  |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
| Quote: | SQL> select ename, deptno,
2 row_number() over (partition by deptno order by null) rn
3 from emp
4 order by rn, deptno
5 /
|
No doubt, its perfect solution for me. But i thik it will not work on Oracle 8.1 (I can check this and report on Saturday Jan 31, 2009. Wish me good luck ).
|
|
|
|
|
|
|
|
|
|
| Re: Cut data into groups [message #384632 is a reply to message #383855] |
Wed, 04 February 2009 09:58 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
I am using windows 2000.
Thanks for your help.
Now, just want to reconfirm another issue on the same case.
I have run the following command
Select * from (
select ename, deptno,
row_number() over (partition by deptno order by null) rn
from emp
order by rn, deptno)
where rn=1
During running this query many times, i changed the value of rn=1 to rn=2 and so on. And every time i got groups according to my need.
The only reconfirmation i require is that is this method will work perfectly even on large number of records? I mean when i will give rn=3, will it always give me SAME SET of records all the time?
My inference says YES.
|
|
|
|
|
|
| Re: Cut data into groups [message #384640 is a reply to message #383685] |
Wed, 04 February 2009 10:36 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
Dear Michel
By exchanging seats i meant was it is not required that CLARK should come in group -1. It can in group-2 BUT once it appear in group then it should be always there in group-2 and should not re-appear in any other group.
I am asking this because, i am going to run this query for many times with different values of "rn" and want to store the output in different files which will be used as input for my special program. I am just worried if a same record reappear in more than 1 group.
As you said:
| Quote: | If you want a deterministic result, you have to specify a deterministic order.
|
So the order by clause in the following will not support to give me deterministic result?
Select * from (
select ename, deptno,
row_number() over (partition by deptno order by null) rn
from emp
order by rn, deptno)
where rn=1
|
|
|
|
|
|
| Re: Cut data into groups [message #384650 is a reply to message #383685] |
Wed, 04 February 2009 11:22 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
Select * from (
select ename, deptno,
row_number() over (partition by deptno order by ename) rn
from emp
order by rn, deptno)
where rn=1
Hence, order by ename will give me deterministic result.
Thank you for your guidance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
