Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Creating sequence for group by in a query?

Re: Creating sequence for group by in a query?

From: Ron <tachoknight_at_gmail.com>
Date: 29 Jun 2006 14:27:34 -0700
Message-ID: <1151616454.434686.322190@y41g2000cwy.googlegroups.com>


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?

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 - 16:27:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US