0

I have 50000 students records in different exam centers having different subjects, in oracle table like this:

Center_Code    Roll_No    PaperCode
    10050         1          711
    10050         2          711
    10050         3          711
    10050         4          711
    10050         10         711
    10050         11         711
    10050         18         711
    10050         21         711
    10050         22         711
    10050         23         711
    10050         24         711
    10050         25         711
    10050         26         711
    10050         27         711
    10050         28         711
    10050         29         711
    10050         30         711
    10050         31         711

I want group of maximum 4 students for each center and want output like this:

   Center_Code paprCode   Roll_Nos                students
    10050        711      1-4                       4
    10050        711      10-11,18-18,21-21         4
    10050        711      22-25                     4
    10050        711      26-29                     4
    10050        711      30-31                     2
1
  • Hi, Welcome to dba.se. Could you please just add create table statements and actual queries? Please read help us help you to improve your question
    – Tom V
    Jan 13, 2016 at 21:05

1 Answer 1

2

This is relatively easy to do, you just need to add some analytic values to your original data set to get it:

with grps as (
select t1.* 
     , trunc((row_number() over (partition by Center_Code, PaperCode order by Roll_No)-1)/4) grp_id
     , roll_no - row_number() over (partition by Center_Code, PaperCode order by Roll_No) rng_id
  from your_data t1
), rngs as (
select center_code
     , papercode
     , to_char(min(roll_no))||'-'||to_char(max(roll_no)) roll_nos
     , min(roll_no) ord
     , count(*) students
     , grp_id
  from grps
 group by center_code, papercode, grp_id, rng_id
)
select center_code
     , papercode
     , LISTAGG(roll_nos, ', ') WITHIN GROUP (ORDER BY ord) roll_nos
     , sum(students) students
  from rngs
  group by center_code, papercode, grp_id;

In the first subfactored query (grps) I'm calculating a grouping id (grp_id) to put the roll_nos into groups of 4, and range id (rng_id) so that each group of 4 roll_nos can be grouped into contiguous ranges.

In the second subfactored query (rngs) the sub ranges of roll_nos are consolidated and counted and an ordering column (ord) is generated.

In the final output query the sub ranges are accumulated with listagg and the final group size is calculated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.