• 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