Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    31

    Unanswered: Assign Group Id by two fields' values

    Hi, I am having difficulties in achieving what is required. I have data which contains b_id, m_id, and s_id and I need to group all the b_id where m_id equal to s_id or s_id is equal to m_id.

    File Layout

    b_id ,m_id ,s_id

    A_1, m_1, null
    B_2, m_1, s_1
    C_5, s_1, m_1

    F_4, m_2, s_2
    H_7, s_2, m_2

    6, m_3, s_4
    7, s_4, m_3
    H_8, s_4, null

    and so on

    The output should be

    b_id, g_id
    A_1, 1111
    B_2, 1111
    C_5, 1111

    F_4, 2222
    H_7, 2222

    6, 3333
    7, 3333
    H_8, 3333

    and so on (I will use sequence number function to assign g_id).

    Thanks in advance.

    Naveed

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    hi, navch:
    you can try this
    with t (b_id, m_id, s_id) as
    ( values
    ( 'A_1', 'm_1', null )
    ,( 'B_2', 'm_1', 's_1' )
    ,( 'C_5', 's_1', 'm_1' )
    ,( 'F_4', 'm_2', 's_2' )
    ,( 'H_7', 's_2', 'm_2' )
    ,( '6', 'm_3', 's_4' )
    ,( '7', 's_4', 'm_3' )
    ,( 'H_8', 's_4', null ) )
    select b_id,row_number() over (partition by group_id order by b_id)
    from (
    select b_id
    ,max_id
    ,min_id
    ,case when
    count(1) over (partition by max_id) > count(1) over (partition by min_id)
    then max_id
    else min_id
    end as group_id
    from (
    select b_id
    ,greatest(value(m_id,s_id),value(s_id,m_id)) as max_id
    ,least(value(m_id,s_id),value(s_id,m_id)) as min_id
    from t
    )
    )
    if your db2 version does not support function greatest and least , you can replace them with case when。

  3. #3
    Join Date
    Jan 2010
    Posts
    31
    Hi,

    No it does not work as I need to have it worked. Anyway, thanks for your reply.

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Naveed,

    try this:
    Code:
    with a (b_id, m_id, s_id) as (values
      ('A_1', 'm_1', null)
    , ('B_2', 'm_1', 's_1')
    , ('C_5', 's_1', 'm_1')
    , ('F_4', 'm_2', 's_2')
    , ('H_7', 's_2', 'm_2')
    , ('6'  , 'm_3', 's_4')
    , ('7'  , 's_4', 'm_3')
    , ('H_8', 's_4', null) 
    )
    , b (b_id, m_id, s_id) as ( 
    select b_id, m_id, coalesce(s_id, max(s_id) over (partition by m_id)) s_id
    from a
    )
    select b_id, dense_rank() over (order by min(m_id, s_id), max(m_id, s_id)) g_id 
    from b
    
     B_ID G_ID
     ---- ----
     A_1     1
     B_2     1
     C_5     1
     F_4     2
     H_7     2
     6       3
     7       3
     H_8     3
    Regards,
    Mark.

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    hi, navch:
    Sorry , it is my fault.
    please replace function row_number() with dense_rank().
    Code:
    WITH
        t
        (
            b_id,
            m_id,
            s_id
        ) AS
        (
            VALUES
            (
                'A_1',
                'm_1',
                NULL
            )
            ,
            ( 'B_2', 'm_1', 's_1' ) ,
            ( 'C_5', 's_1', 'm_1' ) ,
            ( 'F_4', 'm_2', 's_2' ) ,
            ( 'H_7', 's_2', 'm_2' ) ,
            ( '6', 'm_3', 's_4' ) ,
            ( '7', 's_4', 'm_3' ) ,
            ( 'H_8', 's_4', NULL )
        )
    SELECT
        b_id,
        dense_rank() over ( ORDER BY group_id)
    FROM
        (
            SELECT
                b_id ,
                max_id ,
                min_id ,
                CASE
                    WHEN COUNT(1) over (partition BY max_id) > COUNT(1) over (partition BY min_id)
                    THEN max_id
                    ELSE min_id
                END AS group_id
            FROM
                (
                    SELECT
                        b_id ,
                        greatest(value(m_id,s_id),value(s_id,m_id)) AS max_id ,
                        least(value(m_id,s_id),value(s_id,m_id))    AS min_id
                    FROM
                        t ) )

  6. #6
    Join Date
    Jan 2010
    Posts
    31
    Thank a lot guys, both approaches work fine. I really appreciate that you guys helped me in timely manner.

    Regards,

    Naveed

Posting Permissions

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