Re: Creating sequence for group by in a query?

Ron wrote:
> Hi Gints-
>
> Well, in actuality, yes, this is what I want, but the problem is that
> the "GROUP" column needs to be unique (it's a key) so while
> dense_rank() does exactly what I want it to do, it starts from 1 every
> time, when I need it to use the sequence. I looked at the function, but
> I don't think I can use the sequence in it, or am I misreading the
> documentation?

Most probably you cannot use sequence. But you can a little bit modify my insert:
INSERT INTO destination_tab
SELECT dense_rank() OVER (ORDER BY entered_date) +   (SELECT max(group_) FROM destination_tab), id from source_tab;

group_ is your column. If there will be index on group_ column then SELECT max(group_) FROM destination_tab should be pretty fast i.e. probably it could get by 1 consistent get from index so the overhead should be minimal.

Of course there should be problems if you'd like to do that concurrently. And also sequence wouldn't help you, sequences can (and will) have gaps, so you wouldn't get continuous numbers anyway.

Gints Plivna
http://www.gplivna.eu/ Received on Thu Jun 29 2006 - 17:01:52 CDT