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.

Big George <jbeteta (AT) gmail (DOT) com> writes:

If I understand your problem, I think you need to re-think your
approach. I'm not sure the values you are getting are what you think they
are.

rownum does not have any relationship to the values in the table i.e.
rownum != id_column. The rownum relates to your result set not your
table data i.e. if id_gorup = 1 and id_column = 1, this does not mean
rownum will be 1.

Consider what the results would be with our cross join if both groups
were missing the same maximum id_column. What happens if both groups are
missing the same id_column?

What are the possible maximum values for id_column? You need to know
this to know if records are missing or not. It appears (at a glance!)
that your select has the implicit assumption that all possible id_column
values will appear somewhere i.e either in group 1 or group 2, but is
this a reasonable assumption?

Tim

--
tcross (at) rapttech dot com dot au

On Oct 4, 3:36*pm, Big George <jbet... (AT) gmail (DOT) com> wrote:
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 --

Big George wrote:
SQL> select * from mytable;

ID_GROUP ID_COLUMN
---------- ----------
1 5
1 9
3 2
3 10


select grp, nr from (
select level nr from dual start with 1 = 1 connect by level <= (select max(id_column) from
mytable)
) numbers,
( select id_group grp, max(id_column) maxcol from mytable group by id_group) groups
where nr <= maxcol
order by grp, nr
/

SQL> /

GRP NR
---------- ----------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
3 1
3 2
3 3
3 4
3 5
3 6
3 7
3 8
3 9
3 10

19 rows selected.