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/

.

Relevant Pages