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