Home » SQL & PL/SQL » SQL & PL/SQL » Cut data into groups (Oracle 8.1 Windows 2000)
Cut data into groups [message #383685] Thu, 29 January 2009 13:16 Go to next message
dude4084
Messages: 221
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 #383687 is a reply to message #383685] Thu, 29 January 2009 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use ROW_NUMBER to create your groups.

Regards
Michel
Re: Cut data into groups [message #383689 is a reply to message #383685] Thu, 29 January 2009 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select ename, deptno,
  2         row_number() over (partition by deptno order by null) rn
  3  from emp
  4  order by rn, deptno
  5  /
ENAME          DEPTNO         RN
---------- ---------- ----------
CLARK              10          1
JONES              20          1
WARD               30          1
KING               10          2
FORD               20          2
TURNER             30          2
MILLER             10          3
ADAMS              20          3
ALLEN              30          3
SMITH              20          4
JAMES              30          4
SCOTT              20          5
BLAKE              30          5
MARTIN             30          6

14 rows selected.

SQL> col rn noprint
SQL> break on rn skip 1
SQL> /
ENAME          DEPTNO
---------- ----------
CLARK              10
JONES              20
WARD               30

KING               10
FORD               20
TURNER             30

MILLER             10
ADAMS              20
ALLEN              30

SMITH              20
JAMES              30

SCOTT              20
BLAKE              30

MARTIN             30


14 rows selected.

Regards
Michel
Re: Cut data into groups [message #383726 is a reply to message #383685] Thu, 29 January 2009 20:28 Go to previous messageGo to next message
dude4084
Messages: 221
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 #383760 is a reply to message #383726] Fri, 30 January 2009 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works since 8.1.6

Regards
Michel
Re: Cut data into groups [message #383851 is a reply to message #383685] Fri, 30 January 2009 13:11 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
and i think i have 8.1.5 Sad
Re: Cut data into groups [message #383855 is a reply to message #383851] Fri, 30 January 2009 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you aware that 8.1.5 was delivered 10 years ago and is no more supported since 2001?
Is your PC running Windows 98?

Regards
Michel
Re: Cut data into groups [message #384632 is a reply to message #383855] Wed, 04 February 2009 09:58 Go to previous messageGo to next message
dude4084
Messages: 221
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 #384635 is a reply to message #384632] Wed, 04 February 2009 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you have no order you will NOT have the same result each time.
This is in your requirement:
Quote:
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".

If you want a deterministic result, you have to specify a deterministic order.

Regards
Michel
Re: Cut data into groups [message #384640 is a reply to message #383685] Wed, 04 February 2009 10:36 Go to previous messageGo to next message
dude4084
Messages: 221
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 #384642 is a reply to message #384640] Wed, 04 February 2009 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"order by null" means I don't care of the order, so no it does not provide a deterministic order.

Regards
Michel
Re: Cut data into groups [message #384650 is a reply to message #383685] Wed, 04 February 2009 11:22 Go to previous message
dude4084
Messages: 221
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.
Previous Topic: Modifying custom types that are payloads on AQ
Next Topic: Numeric check
Goto Forum:
  


Current Time: Sat Dec 10 05:01:44 CST 2016

Total time taken to generate the page: 0.11017 seconds