Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    76

    Unanswered: Analytical Functions

    Hi,

    I'm having an problem regarding the way I gather some data from database.
    Imagine I have the following table:

    Code:
    --CREATION OF A DUMMY TABLE:
    create table DUMMY_TABLE
    (
      cod_dummy  number not null,
      desc_dummy varchar2(150) not null,
      data_dummy date not null,
      deleted    number(1) default 0
    )
    tablespace <namespace>
      storage
      (
        initial 64K
        minextents 1
        maxextents unlimited
      );
    
    alter table DUMMY_TABLE
      add constraint PK_DUMMY_TABLE primary key (COD_DUMMY);
    And that, its data is:
    Code:
    -- INSERTION OF SOME TEST RECORDS
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (1, 'Number 1', sysdate-100, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (2, 'Number 2', sysdate-90, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (3, 'Number 3', sysdate-80, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (4, 'Number 4', sysdate-70, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (5, 'Number 5', sysdate-60, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (6, 'Number 6', sysdate-50, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (7, 'Number 7', sysdate-40, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (8, 'Number 8', sysdate-30, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (9, 'Number 9', sysdate-20, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (10, 'Number 10', sysdate-10, 0);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (12, 'Number 12 - Deprecated', sysdate-55, 1);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (13, 'Number 13 - Deprecated', sysdate-35, 1);
    
    insert into dummy_table (cod_dummy, desc_dummy, data_dummy, deleted)
    values (14, 'Number 14 - Deprecated', sysdate-25, 1);
    Now imagine that I need to gather info from that table, partitioned in a way that it brings me, the following result:
    Code:
    -- RESULT WANTED:
    -- Imagine that we dont know the total count of records in the DUMMY_TABLE
    -- I want to retrieve, the first and last COD_DUMMY, grouped by Groups of 3, by example:
    -- FIRST_COD  |  LAST_CODE  | NUMBER_GROUPING
    --     1             |      3             |        1
    --     4             |      6             |        2
    --     7             |      9             |        3
    --     10           |      12            |        4
    --     13           |      14            |        5
    I've read a little about DENSE_RANKS and PARTITIONED BY functions but I'm not being able to get the result.

    Can anyone help me?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Quote Originally Posted by aucrun View Post
    Hi,

    I'm having an problem . . .
    . . . E t c . . .
    I've read a little about DENSE_RANKS and PARTITIONED BY functions but I'm not being able to get the result.

    Can anyone help me?
    No analytical function necessary, try this:
    Code:
    SQL> SELECT MIN ( Cod_Dummy) First_Code, MAX ( Cod_Dummy) Last_Code
      2       , TRUNC ( (Cod_Dummy - 1) / 3) + 1 Member_Grouping
      3    FROM Dummy_Table
      4   GROUP BY TRUNC ( (Cod_Dummy - 1) / 3)
      5*  ORDER BY 2, 1
    SQL> /
    
    FIRST_CODE  LAST_CODE MEMBER_GROUPING
    ---------- ---------- ---------------
             1          3               1
             4          6               2
             7          9               3
            10         12               4
            13         14               5
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    76
    It's it!

    Thanks a lot.

Tags for this Thread

Posting Permissions

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