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

> 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.

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