Re: Creating sequence for group by in a query?

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/

.

Relevant Pages

  • Re: How do I number a list out of sequence?
    ... How are you inserting these numbers? ... Save the sequence and its associated text (as a formatted autocorrect entry) ... The SEQ fields so inserted will not *automatically* update. ...
    (microsoft.public.word.docmanagement)
  • Re: Creating sequence for group by in a query?
    ... Gints Plivna wrote: ... So the sequence is based on the group by of the entered_date field. ... BTW table where you are inserting cannot exist unless you enclose group ...
    (comp.databases.oracle.server)
  • RE: Access Append Query to update Sequence Generator in Oracle
    ... was that the Sequence Generator in Oracle remained at the last number PRIOR ... to the insert query, so, even though the new larger numbers went in, Oracle ... If this is a multi-user app, you don't know if someone else is inserting a ...
    (microsoft.public.access.queries)
  • Re: Programmatically inserting into a row using ResultSet
    ... for that matter) while you were inserting. ... begin transaction ... I'm first doing a query on the table, ... and it updates with the next value in a sequence. ...
    (comp.lang.java.databases)
  • Re: How to insert a number in a vector
    ... > Could anyone help me in updating a vector by inserting ... > some numbers at specified index. ... > This I needed to generate Stern-Brocot sequence. ...
    (comp.soft-sys.matlab)