Using Dense Rank

  • CREATE TABLE [REV_BINDERS](

    [OFFICE_ID] [int] NOT NULL,

    [BINDER_ID] [int] NOT NULL,

    [BINDER_NO] [varchar](7) NULL,

    )

    INSERT INTO [REV_BINDERS]

    ([OFFICE_ID],[BINDER_ID],[BINDER_NO])

    select 2105110,10,0101 union all

    select 2105110,12,0102 union all

    select 2105110,13,0102 union all

    select 2105110,14,0103 union all

    select 2105110,15,0103 union all

    select 2105110,16,0104 union all

    select 2105110,17,0105 union all

    select 2105110,18,0105 union all

    select 2105110,19,0105 union all

    select 2105110,20,0106

    with emp as (

    select OFFICE_ID,BINDER_ID,BINDER_NO,dense_rank() over (PARTITION BY BINDER_NO ORDER BY BINDER_ID ) as denserank from REV_BINDERS )

    select * from emp

    I want to get those BINDER_NO which don't repeat according to BINDER_ID using above query.

  • I am not sure that I understand your question correctly, but here is what I think you are looking for.

    ;WITH CTE as (select row_number() OVER(partition by Binder_no ORDER BY Binder_id)

    AS rn,office_id,Binder_id,Binder_no

    FROM [Rev_Binders])

    SELECT * FROM cte where rn = 1

    Results:

    rnoffice_idBinder_idBinder_no

    12105110 10 101

    12105110 12 102

    12105110 14 103

    12105110 16 104

    12105110 17 105

    12105110 20 106

    If my assumption as to what you require is NOT correct, please post your desired answer.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hey hi dear..

    I want this output using dense rank eliminating repeating binder no

    OFFICE_ID BINDER_ID BINDER_NO denserank

    2105110 10 1011

    2105110 16 1041

    2105110 20 1061

  • saltpepo (12/1/2012)


    Hey hi dear..

    I want this output using dense rank eliminating repeating binder no

    OFFICE_ID BINDER_ID BINDER_NO denserank

    2105110 10 1011

    2105110 16 1041

    2105110 20 1061

    Based on the above, I think what you are looking for is the following:

    CREATE TABLE [REV_BINDERS](

    [OFFICE_ID] [int] NOT NULL,

    [BINDER_ID] [int] NOT NULL,

    [BINDER_NO] [varchar](7) NULL,

    );

    go

    INSERT INTO [REV_BINDERS]

    ([OFFICE_ID],[BINDER_ID],[BINDER_NO])

    select 2105110,10,0101 union all

    select 2105110,12,0102 union all

    select 2105110,13,0102 union all

    select 2105110,14,0103 union all

    select 2105110,15,0103 union all

    select 2105110,16,0104 union all

    select 2105110,17,0105 union all

    select 2105110,18,0105 union all

    select 2105110,19,0105 union all

    select 2105110,20,0106;

    go

    with emp as (

    select

    OFFICE_ID,

    BINDER_ID,

    BINDER_NO,

    count(*) over (PARTITION BY OFFICE_ID, BINDER_NO) as BinderCnt

    from

    REV_BINDERS )

    select * from emp where BinderCnt = 1;

    go

    DROP TABLE [REV_BINDERS];

    go

  • I m exactly looking for this but can i obtain same result using dense rank??

  • Now i realized what i exactly want.

    CREATE TABLE [REV_BINDERS](

    [OFFICE_ID] [int] NOT NULL,

    [BINDER_ID] [int] NOT NULL,

    [BINDER_NO] [varchar](7) NULL,

    );

    go

    INSERT INTO [REV_BINDERS]

    ([OFFICE_ID],[BINDER_ID],[BINDER_NO])

    select 2105110 , 1,0101 union all

    select 2105110,2,0102union all

    select 2105110,3,0103union all

    select 2105110,4,0104union all

    select 2105110,5,0105union all

    select 2105110,6,0108union all

    select 2101110,261,0101union all

    select 2101110,262,0102union all

    select 2101110,263,0103union all

    select 2101110,264,0104union all

    select 2101110,265,0105union all

    select 2101110,266,0106

    Using count(*) or denserank can i obtain following result?

    2105110,6,0108

    2101110,266,0106

    Kindly help

  • SELECT

    rb.OFFICE_ID,

    MAX(rb.BINDER_ID),

    MAX(rb.BINDER_NO)

    FROM dbo.REV_BINDERS AS rb

    GROUP BY

    rb.OFFICE_ID

    ORDER BY

    rb.OFFICE_ID;

    +-----------+------------------+------------------+

    | OFFICE_ID | (No column name) | (No column name) |

    +-----------+------------------+------------------+

    | 2101110 | 266 | 106 |

    | 2105110 | 6 | 108 |

    +-----------+------------------+------------------+

  • No dear thats what not i looking for.I have to eliminate repeating binder no.Max concept not applicable for following data.CREATE TABLE [REV_BINDERS](

    [OFFICE_ID] [int] NOT NULL,

    [BINDER_ID] [int] NOT NULL,

    [BINDER_NO] [varchar](7) NULL,

    );

    go

    INSERT INTO [REV_BINDERS]

    ([OFFICE_ID],[BINDER_ID],[BINDER_NO])

    select 2105110 , 1,0101 union all

    select 2105110,2,0102union all

    select 2105110,3,0103union all

    select 2105110,4,0104union all

    select 2105110,5,0108union all

    select 2105110,6,0105union all

    select 2101110,261,0101union all

    select 2101110,262,0102union all

    select 2101110,263,0103union all

    select 2101110,264,0104union all

    select 2101110,265,0106union all

    select 2101110,266,0105

    For above data required output is.

    2105110,5,0108

    2101110,265,0106

    Only those binder no which are not repeating.

  • SELECT

    t.OFFICE_ID,

    t.BINDER_NO,

    t.BINDER_ID

    FROM

    (

    SELECT

    rb.OFFICE_ID,

    rb.BINDER_NO,

    group_count = COUNT_BIG(*) OVER (PARTITION BY rb.BINDER_NO),

    rb.BINDER_ID

    FROM dbo.REV_BINDERS AS rb

    ) AS t

    WHERE

    t.group_count = 1;

    +-----------+-----------+-----------+

    | OFFICE_ID | BINDER_NO | BINDER_ID |

    +-----------+-----------+-----------+

    | 2101110 | 106 | 265 |

    | 2105110 | 108 | 5 |

    +-----------+-----------+-----------+

  • Or:

    SELECT

    rb.OFFICE_ID,

    rb.BINDER_NO,

    rb.BINDER_ID

    FROM dbo.REV_BINDERS AS rb

    WHERE

    EXISTS

    (

    SELECT *

    FROM dbo.REV_BINDERS AS rb2

    WHERE

    rb2.BINDER_NO = rb.BINDER_NO

    GROUP BY

    rb2.BINDER_NO

    HAVING

    COUNT_BIG(*) = 1

    );

    +-----------+-----------+-----------+

    | OFFICE_ID | BINDER_NO | BINDER_ID |

    +-----------+-----------+-----------+

    | 2101110 | 106 | 265 |

    | 2105110 | 108 | 5 |

    +-----------+-----------+-----------+

  • Or:

    SELECT

    rb.OFFICE_ID,

    rb.BINDER_NO,

    rb.BINDER_ID

    FROM dbo.REV_BINDERS AS rb

    WHERE

    rb.BINDER_NO IN

    (

    SELECT

    rb.BINDER_NO

    FROM dbo.REV_BINDERS AS rb

    GROUP BY

    rb.BINDER_NO

    HAVING

    COUNT_BIG(*) = 1

    );

    +-----------+-----------+-----------+

    | OFFICE_ID | BINDER_NO | BINDER_ID |

    +-----------+-----------+-----------+

    | 2105110 | 108 | 5 |

    | 2101110 | 106 | 265 |

    +-----------+-----------+-----------+

  • Or:

    SELECT

    OFFICE_ID = MAX(t.OFFICE_ID),

    BINDER_ID = MAX(t.BINDER_ID),

    t.BINDER_NO

    FROM

    (

    SELECT

    rb.*,

    rn = ROW_NUMBER() OVER (

    PARTITION BY rb.BINDER_NO

    ORDER BY rb.BINDER_NO)

    FROM dbo.REV_BINDERS AS rb

    ) AS t

    GROUP BY

    t.BINDER_NO

    HAVING

    MAX(t.rn) = 1;

    +-----------+-----------+-----------+

    | OFFICE_ID | BINDER_NO | BINDER_ID |

    +-----------+-----------+-----------+

    | 2101110 | 106 | 265 |

    | 2105110 | 108 | 5 |

    +-----------+-----------+-----------+

  • I still like mine, just had to modify the partition by clause. I believe this is also one that Paul White post.

    CREATE TABLE [REV_BINDERS](

    [OFFICE_ID] [int] NOT NULL,

    [BINDER_ID] [int] NOT NULL,

    [BINDER_NO] [varchar](7) NULL,

    );

    go

    INSERT INTO [REV_BINDERS]

    ([OFFICE_ID],[BINDER_ID],[BINDER_NO])

    select 2105110 , 1,0101 union all

    select 2105110,2,0102union all

    select 2105110,3,0103union all

    select 2105110,4,0104union all

    select 2105110,5,0108union all

    select 2105110,6,0105union all

    select 2101110,261,0101union all

    select 2101110,262,0102union all

    select 2101110,263,0103union all

    select 2101110,264,0104union all

    select 2101110,265,0106union all

    select 2101110,266,0105;

    go

    with emp as (

    select

    OFFICE_ID,

    BINDER_ID,

    BINDER_NO,

    count(*) over (PARTITION BY BINDER_NO) as BinderCnt

    from

    REV_BINDERS )

    select * from emp where BinderCnt = 1;

    go

    drop table [REV_BINDERS];

    go

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply