Using Dense Rank

Question

Post reply

Using Dense Rank

saltpepo

Old Hand

Points: 396
More actions
December 1, 2012 at 2:20 am

#265598

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.

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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]

saltpepo Old Hand Points: 396 More actions · Answer 2

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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

saltpepo Old Hand Points: 396 More actions · Answer 4

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

saltpepo Old Hand Points: 396 More actions · Answer 5

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

Paul White SSC Guru Points: 150467 More actions · Answer 6

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 |

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

saltpepo Old Hand Points: 396 More actions · Answer 7

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.

Paul White SSC Guru Points: 150467 More actions · Answer 8

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 |

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

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 |

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 10

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 |

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 11

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 |

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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