Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    25

    Unanswered: First occurence count

    How can I flag the first occurence of data??
    Eg.
    I have follwing table.

    INVOICE_ID SALE_ID OBLIGOR_ID
    1 123 [NULL]
    2 693 12
    2 254 20
    3 789 96
    3 789 59
    3 789 26

    Now there is one more column call invoice_count. It should display 1 for first occurence of every invoice_id and 0 for repeated occurence.
    i.e
    The result should look like this.

    INVOICE_ID SALE_ID OBLIGOR_ID invoice_count
    1 123 [NULL] 1
    2 693 12 1
    2 254 20 0
    3 789 96 1
    3 789 59 0
    3 789 26 0



    Can you please help me out.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Data in a table have no intrinsic order. How do you define "first occurrence"?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Data in a table have no intrinsic order.
    Yes! He is right.
    In the following example, there is no gurantee to be chosen specific rows.
    Code:
    SELECT t.*
         , 1 - SIGN( ROW_NUMBER() OVER(PARTITION BY invoice_id) - 1 )
           AS invoice_count
      FROM sample_data t
    ;
    ------------------------------------------------------------------------------
    
    INVOICE_ID  SALE_ID     OBLIGOR_ID  INVOICE_COUNT
    ----------- ----------- ----------- -------------
              1         123           -             1
              2         693          12             1
              2         254          20             0
              3         789          96             1
              3         789          59             0
              3         789          26             0
    
      6 record(s) selected.

  4. #4
    Join Date
    Jun 2010
    Posts
    25
    I have invoice_ID, sale_ID and obligor_ID already present in the table order by invoice_id, sale_ID and obligor_id.

    But the above set of code worked for me eventhough me invoice_id field is of Character Type.
    But I did not get the concept behind it, Can you please elaborate on this?


    Thanks...
    Last edited by smartcooldevil; 08-11-10 at 11:01.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    already present in the table order by invoice_id, sale_ID and obligor_id.
    No!
    Rows in a table is not ordered.
    If the result of "SELECT * FROM table" showed a sequence of rows,
    the order is eventualy.
    You may get result with different order from later query.
    For example, reorg table, create index or adding conditions to the query may change the resulting order.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Another solution is possible

    Another solution without ROW_NUMBER

    Code:
    with sample_data 
    (INVOICE_ID, SALE_ID, OBLIGOR_ID, unique_id) as
    (
    select 1, 123, nullif(0,0) , generate_unique() 
    from sysibm.sysdummy1 union all
    select 2, 693, 12, generate_unique() 
    from sysibm.sysdummy1 union all
    select 2, 254, 20, generate_unique() 
    from sysibm.sysdummy1 union all
    select 3, 789, 96, generate_unique() 
    from sysibm.sysdummy1 union all
    select 3, 789, 59, generate_unique() 
    from sysibm.sysdummy1 union all
    select 3, 789, 26, generate_unique() 
    from sysibm.sysdummy1
    ) 
    , sample_min_uid (INVOICE_ID, min_uid) as 
    (select INVOICE_ID, min(unique_id)
       from sample_data group by INVOICE_ID
    ) 
    
    select sd.INVOICE_ID, sd.SALE_ID, sd.OBLIGOR_ID, 
    case when sd.unique_id = md.min_uid 
         then 1 
         else 0 
    end  as INVOICE_COUNT
    from 
    sample_data    sd
    join
    sample_min_uid md 
    on sd.INVOICE_ID = md.INVOICE_ID
    Result:

    INVOICE_ID SALE_ID OBLIGOR_ID INVOICE_COUNT
    1 123--- 1
    2 693 12 1
    2 254 20 0
    3 789 96 1
    3 789 26 0
    3 789 59 0
    Lenny
    Last edited by Lenny77; 08-11-10 at 19:00.

Posting Permissions

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