Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Updating a column using RANK()

    DESC TAB1;
    Name Null? Type
    ---------------- ---------- -------------
    EFFDT DATE
    EFFSEQ NOT NULL NUMBER(38)

    SQL> SELECT EFFDT,EFFSEQ FROM TAB1;

    EFFDT EFFSEQ
    --------- ----------
    27-SEP-06 0
    27-SEP-06 0
    28-SEP-06 0
    28-SEP-06 0
    29-SEP-06 0
    29-SEP-06 0
    29-SEP-04 0
    29-SEP-06 0
    01-SEP-06 0
    07-OCT-06 0
    07-OCT-06 0
    I have a table TAB1 in which effseq has to be populated as RANK. Each time a transaction is made on a day(EFFDT)
    EFFSEQ is populated as 1. If another transaction is made on the same day, then EFFSEQ is populated as 2 and so on.
    I populated this in RANK as shown below.

    SQL> ED
    Wrote file afiedt.buf
    1 select EFFDT,EFFSEQ,rank() over (partition by T1.effdt order by rownum) as rank
    2* from TAB1 T1
    3 /


    EFFDT EFFSEQ RANK
    --------- ---------- ----------
    01-JAN-90 0 1
    01-JAN-90 0 2
    01-SEP-06 0 1
    01-SEP-06 0 2
    19-SEP-06 0 1
    26-SEP-06 0 1
    26-SEP-06 0 2
    27-SEP-06 0 1
    27-SEP-06 0 2
    27-SEP-06 0 3
    27-SEP-06 0 4
    27-SEP-06 0 5
    27-SEP-06 0 6
    But I need to update EFFSEQ with this RANK which I'm not able to do. I gave the following Update statement and it gives me this error.Perhaps, my update statement is wrong

    update TAB1 T1 set T1.effseq =
    (select T2.rank from
    (select rank() over (partition by T1.effdt order by rownum) as rank
    from TAB1 T1) T2
    where T1.effdt=T2.effdt)

    SQL> /
    where T1.effdt=T2.effdt)
    *
    ERROR at line 5:
    ORA-00904: "T2"."EFFDT": invalid identifier

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Why populate and not just SELECT it when required ? (it could possible be a bug if you update and then select it, since by the time you update and select it, someone else could have made changes that impacts the ranks)

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    going forward (ie: after you update all this)
    is this only for transactions that happen in real time?

    per day, do you want the rank according to the exact time of that day?
    earliest to latest?

    what if two transactions happen at the exact same time?
    who gets what rank?

    those two columns cannot be the PK.
    what is the PK in the table?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2005
    Posts
    276
    Later I have to insert into LOC in another table TAB2 by picking the max(EFFSEQ), like if there are 10 transactions in a day then pick 10 into LOC.
    Thats the reason I need to update in TAB1 and then INSERT INTO LOC IN TAB2

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    SQL> DESC TAB1
    Name Null? Type
    ----------------------- -------- ----------------------------
    BU_UNIT NOT NULL VARCHAR2(5)
    AST_ID NOT NULL VARCHAR2(12)
    EFFDT DATE
    EFFSEQ NOT NULL NUMBER(38)
    LOCATION NOT NULL VARCHAR2(10)
    DOCUMENT_ID NOT NULL VARCHAR2(12)
    AUTH_STATUS NOT NULL VARCHAR2(1)
    AUTHORIZATION_NAME NOT NULL VARCHAR2(30)
    AUTHORIZATION_DT DATE
    RANK NUMBER(3)

    None of the columns has a PK on it.

    SQL> select constraint_name, constraint_type from all_constraints where table_name='TAB1';

    CONSTRAINT_NAME C
    ------------------------------ -
    SYS_C00172854 C
    SYS_C00172855 C
    SYS_C00172856 C
    SYS_C00172857 C
    SYS_C00172858 C
    SYS_C00172859 C
    SYS_C00172860 C

    7 rows selected.
    I dont need the update to be done with the exact time.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by nandinir
    I dont need the update to be done with the exact time.
    so if you have 10 entries for one day how do you determine which gets a
    rank of 1 and which gets a rank of 10? randomly?

    you do realize that any date is actually a day and timestamp right?

    try alter session set nls_date_format = 'mmddyyyy hh24:mi:ss';

    this will show what the actual date and time are (which are there no matter what, but you just were not showing them). when you run your analytic statement it is actually ordering/ranking by time.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2005
    Posts
    276
    The ast_id in TAB1 can be the key column.
    Similarly I have ast_id in TAB2 TOO

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    RANK: The data in the group is sorted by the ORDER BY clause and then a numeric ranking is assigned to each row starting with 1. Rows with the same values of the ORDER BY expressions receive the same rank; however if this occurs the rank numbers will subsequently skip.

    ie: if two rows are RANKed 1, there will be no number 2-rank and the next assigned value will be 3.

    is that how you want the ranking to work? (just need to make sure you want a rank and not a count).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2005
    Posts
    276
    No, I want a count. I want to basically see the #of transactions per day and then insert the max(effseq)(similar to count here) for some other field in another table.
    Like
    effdt count#
    29-sep-06 1
    29-sep-06 2
    30-sep-06 1
    29-sep-06 3---> pick this one for loc in tab2
    30-sep-06 2
    04-oct-06 1
    05-oct-06 1
    Last edited by nandinir; 11-10-06 at 13:39.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you want to insert into a second table, the total rows in the first for each effective date, why bother with a counter. It will not work and is a total waste of time, use something like the following.


    insert into the_other_table
    SELECT EFFDT,COUNT(*)
    FROM TAB1
    GROUP BY EFFDT;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Jul 2005
    Posts
    276
    My code is like this:
    insert into other_tab
    (
    col1..
    col2
    ..
    location
    )
    select
    ..
    ..
    ,(select max(effseq) from tab1 group by effdt,id,unit)
    from tab1, tab2
    where
    <condn>
    I get this error:
    Single row sub query returns multiple rows.

    Then I inserted 0 when loading data and then tried to update it separately as follows:

    update other_tab set location=(select max(effseq) from tab1 group by effdt, id, unit);
    ERROR at line 1:
    ORA-01427: single-row subquery returns more than one row
    I tried using case when loading data but I couldn't do it correctly.

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    the select to count the sequencing per day:
    PHP Code:
    select
       ast_id
    ,
       
    effdt,
       
    count(1over (partition by to_char(effdt,'yyyymmdd'order by effdtast_id new_seq
    from tab1

    to update effseq with that sequencing:
    PHP Code:
    declare

       
    cursor c1 is
          select
             ast_id
    ,
             
    effdt,
             
    count(1over (partition by to_char(effdt,'yyyymmdd'order by effdtast_id new_seq
          from tab1 
    for update;

    begin

       
    for i in c1 loop

          update tab1
             set effseq 
    i.new_seq
          where current of c1
    ;

       
    end loop;

       
    commit;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •