Re: Help on query, how to show sequential numbers for each group

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 5 Oct 2010 06:57:07 -0700 (PDT)
Message-ID: <0005c5ed-8001-4c7f-a4a0-28db82110f8e_at_i13g2000yqd.googlegroups.com>

On Oct 4, 3:36 pm, Big George <jbet..._at_gmail.com> wrote:
> Hello,
>
> MyTable has missing records:
>
> id_group  id_column
> -------------------------
> 1      1
> 1      2
> 1      3
> 1      5
> 1      9
> 2      1
> 2      2
> 2      4
> 2      6
>
> As you see, it could be missed record 4,6,7 and 8 of group 1 and
> record 3 and 5 of group 2. That's is an example.
>
> I need to show all sequential records, incluing missing ones, like
> this:
>
> Id_group      Id_column
> -------------------------------------------------
> 1                  1
> 1                  2
> 1                  3
> 1                  4
> 1                  5
> 1                  6
> 1                  7
> 1                  8
> 1                  9
> 2                  1
> 2                  2
> 2                  3
> 2                  4
> 2                  5
> 2                  6
>
> I try this query:
>
> Select G.id_group, C.rownum id_column
> from (select distinct id_group from MyTable) G
> cross join (SELECT rownum FROM dual CONNECT BY LEVEL <= (select
> max(id_column) from MyTable)) C
>
> but it shows record 7, 8 and 9 as members of group 2.
>
> Could somebody help me, please? Thanks.

All you need to do is generate the list of all possible values for the group and number of members in the group and then outer join your data to all possible values.

Here is a traditional way to generate all possible sequential values. You might need to use subqueries on your data to find a minimum starting value and a maximum ending value for the range.

There are 3 groups of 9 possible members in the example

SQL> l
  1 select grp, member
  2 from
  3 (select rownum as grp from all_objects where rownum < 4 ) g   4 ,(select rownum as member from all_objects where rownum < 10) m   5 order by
  6* grp, member
SQL> /        GRP MEMBER
---------- ----------

         1          1
         1          2
         1          3
         1          4
         1          5
         1          6
         1          7
         1          8
         1          9
         2          1
         2          2
         2          3
   <snip>
         3          1
         3          2
         3          3
         3          4
         3          5
         3          6
         3          7
         3          8
         3          9

27 rows selected.

The MODEL statement could probably be used to perform this action.

HTH -- Mark D Powell -- Received on Tue Oct 05 2010 - 08:57:07 CDT