Home » SQL & PL/SQL » SQL & PL/SQL » Grouping in sets of 100 rows (Oracle 10g)
Grouping in sets of 100 rows [message #336743] Mon, 28 July 2008 16:23 Go to next message
pkirangi
Messages: 74
Registered: August 2005
Member
I have a requirement where I need to group records as sets of 100 records and then find min and max of Primary Key id's in those sets of 100.

For e.g. in EMP table
Lets say there are 1000 rows
I need

Min(emp id)  Max(emp id) count
============ =========== =======
a            b           100
c            d           100
e            f           100
g            h           100
i            j           100
k            l           100
m            n           100
o            p           100
q            r           100
s            t           100


Primary id's should be in ascending order.

How can I acheive this?

Thanks
Re: Grouping in sets of 100 rows [message #336750 is a reply to message #336743] Mon, 28 July 2008 18:14 Go to previous messageGo to next message
Lynn T
Messages: 8
Registered: July 2008
Junior Member
I'm new to this forum, and I believe my instructions are to provide hints not code, so here's my hints. I used the User_Catalog table as a test, and it does work correctly. There may be a slicker mathematical way, but this gets the job done.

I first used rownum to assign numbers to each row in order.
Then with a higher-level query, I selected the row # and primary key from that subquery where the rownum when divided by 100 (mod function) gave me either 0 or 1. This left these.
ROW#
----
   1
 100
 101
 200
 201
 300
 301
 400
 401

Using the 'lead/over partition' function I then put 100 on the same line with 1, etc.

  ROW#      LEAD#
------ ----------
     1        100
   100        101
   101        200
   200        201
   201        300
   300        301
   301        400
   400        401
   401 .

Finally, I ruled out all those rows where row# divided evenly by 100.
  ROW#      LEAD#
------ ----------
     1        100
   101        200
   201        300
   301        400
   401 .

Let me know if this doesn't make sense.

[Mod-Edit: Frank added code-tags to improve readability]

[Updated on: Tue, 29 July 2008 00:52] by Moderator

Report message to a moderator

Re: Grouping in sets of 100 rows [message #336790 is a reply to message #336743] Tue, 29 July 2008 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Say 10 instead of 100:
SQL> select empno, ename, 
  2         trunc((row_number() over(order by ename)-1)/10) grp
  3  from emp
  4  order by ename
  5  /
     EMPNO ENAME             GRP
---------- ---------- ----------
      7499 ALLEN               0
      7698 BLAKE               0
      7782 CLARK               0
      7902 FORD                0
      7900 JAMES               0
      7566 JONES               0
      7839 KING                0
      7654 MARTIN              0
      7934 MILLER              0
      7369 SMITH               0
      7844 TURNER              1
      7521 WARD                1

12 rows selected.

Regards
Michel
Re: Grouping in sets of 100 rows [message #336814 is a reply to message #336743] Tue, 29 July 2008 02:02 Go to previous messageGo to next message
raghu2110
Messages: 5
Registered: July 2008
Location: INDIA
Junior Member
hi michel,

Can u explain me how the particular query works

trunc((row_number() over(order by ename)-1)/10)-how the rows are ordered..

To be more specific i dont understand the how

((row_number() over(order by ename)-1)/10
works

Regards
P.Raghuveer
Re: Grouping in sets of 100 rows [message #336816 is a reply to message #336814] Tue, 29 July 2008 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does this answer the question?
SQL> select empno, ename, 
  2         row_number() over(order by ename) rn,
  3         row_number() over(order by ename)-1 "RN-1",
  4         (row_number() over(order by ename)-1)/10 "(RN-1)/10",
  5         trunc((row_number() over(order by ename)-1)/10) grp
  6  from emp
  7  order by ename
  8  /
     EMPNO ENAME              RN       RN-1  (RN-1)/10        GRP
---------- ---------- ---------- ---------- ---------- ----------
      7499 ALLEN               1          0          0          0
      7698 BLAKE               2          1         .1          0
      7782 CLARK               3          2         .2          0
      7902 FORD                4          3         .3          0
      7900 JAMES               5          4         .4          0
      7566 JONES               6          5         .5          0
      7839 KING                7          6         .6          0
      7654 MARTIN              8          7         .7          0
      7934 MILLER              9          8         .8          0
      7369 SMITH              10          9         .9          0
      7844 TURNER             11         10          1          1
      7521 WARD               12         11        1.1          1

12 rows selected.

Regards
Michel
Re: Grouping in sets of 100 rows [message #336924 is a reply to message #336743] Tue, 29 July 2008 06:47 Go to previous message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks Lynn and Michel.
Both of the methods work well.
But I liked the idea from Michel, to assign group numbers and then get the min and max in those group's. This works well especially since it gets the job done in a single querry.

I am not in front of my work computer
But would this also not have worked?

select empno, ename, 
trunc((rownum -1)/10) grp
from emp
 order by ename


Thanks
Previous Topic: number format
Next Topic: Compare oracle timestamp
Goto Forum:
  


Current Time: Mon Dec 05 08:42:02 CST 2016

Total time taken to generate the page: 0.12728 seconds