Re: Creating sequence for group by in a query?

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:11:17 CDT