Re: Creating sequence for group by in a query?

Hi Gints-

Wow, that works perfectly, thanks a lot!

BTW, you're right...group was a bad choice...the real column name is long and weird and didn't want to muddy the waters with it.

Thanks again,

Ron

Gints Plivna wrote:
> Ron wrote:
> > Hi all-
> >
> > I am trying to insert into a table that takes two number fields. The
> > first field I want to generate with a sequence, the second is an ID.
> > The issue is that the base data has a date field. So I have a table
> > like:
> >
> > ID ENTERED_DATE
> > 32423 5/5/06
> > 34644 5/5/06
> > 11907 6/1/06
> > 95996 6/16/06
> > 94311 6/16/06
> >
> > So that the table I'm inserting looks like:
> >
> > GROUP ID
> > 1 32423
> > 1 32423
> > 2 11907
> > 3 95996
> > 3 94311
> >
> > So the sequence is based on the group by of the entered_date field. Is
> > this possible to do in a single sql statement?
> >
> > Thanks,
> >
> > Ron
>
> BTW table where you are inserting cannot exist unless you enclose group
> in double quotes, but you really don't want to do that. Group is
> reserved word.
>
> OK and now here is insert:
> INSERT INTO destination_tab
> SELECT dense_rank() OVER (ORDER BY entered_date), id
> FROM source_tab;
>
> Gints Plivna
> http://www.gplivna.eu/
Received on Thu Jun 29 2006 - 14:47:44 CDT