locked
Count sequence within multiple groups RRS feed

  • Question

  • Hello all,

    I'm trying to figure out how to count the number of entries within a group.

    I have this simple query that returns the Job # and Operation Code below.  For every job #, there could be a multiude of Operations.  I would like to number each operation in order of sequence.

    For example

    JOB      Operation Code       Op Seq

    1            Drill                       1

    1           Press                     2

    1           Assemble               3

    2           Weld                     1

    2           Drill                       2

    2           Cut                       3

    2           Paint                     4

    Any ideas?

    Thanks,

    Jasper

    SELECT

    J.Job_No AS Job_No,
    JOP.Op_Code AS Op_Code


    FROM Part_v_Job AS J

    JOIN Part_v_Job_Op AS JOP
       ON J.Job_Key = JOP.Job_Key

    ORDER BY J.Job_No, JOP.Op_No

    Friday, May 20, 2011 4:09 PM

Answers

  • SELECT
    J.Job_No AS Job_No,
    JOP.Op_Code AS Op_Code,
    ROW_NUMBER() OVER (PARTITION BY J.Job_No ORDER BY JOP.op_Code) AS OP_SEQ
    FROM Part_v_Job AS J
    
    JOIN Part_v_Job_Op AS JOP
      ON J.Job_Key = JOP.Job_Key
    
    ORDER BY J.Job_No, JOP.Op_No
    
    
    

    You can use ROW_NUMBER() function to do this,

     


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Proposed as answer by bradgl Friday, May 20, 2011 8:33 PM
    • Marked as answer by Kalman Toth Friday, May 27, 2011 11:01 AM
    Friday, May 20, 2011 5:05 PM

All replies

  • SELECT
    J.Job_No AS Job_No,
    JOP.Op_Code AS Op_Code,
    ROW_NUMBER() OVER (PARTITION BY J.Job_No ORDER BY JOP.op_Code) AS OP_SEQ
    FROM Part_v_Job AS J
    
    JOIN Part_v_Job_Op AS JOP
      ON J.Job_Key = JOP.Job_Key
    
    ORDER BY J.Job_No, JOP.Op_No
    
    
    

    You can use ROW_NUMBER() function to do this,

     


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Proposed as answer by bradgl Friday, May 20, 2011 8:33 PM
    • Marked as answer by Kalman Toth Friday, May 27, 2011 11:01 AM
    Friday, May 20, 2011 5:05 PM
  • Hi Jason !

    Please post DDL code and sample data and expected output. I am not seeing anything wrong with your query.

    Thanks, Hasham

    Friday, May 20, 2011 5:07 PM
    Answerer
  • Perfect!!

    Thanks,

    Jasper

    Friday, May 20, 2011 7:03 PM