Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using Dense Rank Expand / Collapse
Author
Message
Posted Saturday, December 01, 2012 2:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
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.
Post #1391633
Posted Saturday, December 01, 2012 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 5,471, Visits: 23,509
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:

rn office_id Binder_id Binder_no
1 2105110 10 101
1 2105110 12 102
1 2105110 14 103
1 2105110 16 104
1 2105110 17 105
1 2105110 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

Before posting a performance problem please read
Post #1391646
Posted Saturday, December 01, 2012 9:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
Hey hi dear..

I want this output using dense rank eliminating repeating binder no

OFFICE_ID BINDER_ID BINDER_NO denserank
2105110 10 101 1
2105110 16 104 1
2105110 20 106 1
Post #1391649
Posted Saturday, December 01, 2012 2:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:23 PM
Points: 22,492, Visits: 30,194
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 101 1
2105110 16 104 1
2105110 20 106 1




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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1391673
Posted Sunday, December 02, 2012 12:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
I m exactly looking for this but can i obtain same result using dense rank??
Post #1391701
Posted Sunday, December 02, 2012 1:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
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 ,0102 union all
select 2105110 ,3 ,0103 union all
select 2105110 ,4 ,0104 union all
select 2105110 ,5 ,0105 union all
select 2105110 ,6 ,0108 union all
select 2101110 ,261 ,0101 union all
select 2101110 ,262 ,0102 union all
select 2101110 ,263 ,0103 union all
select 2101110 ,264 ,0104 union all
select 2101110 ,265 ,0105 union all
select 2101110 ,266 ,0106

Using count(*) or denserank can i obtain following result?
2105110 ,6 ,0108
2101110 ,266 ,0106
Kindly help
Post #1391705
Posted Sunday, December 02, 2012 2:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1391706
Posted Sunday, December 02, 2012 3:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64
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 ,0102 union all
select 2105110 ,3 ,0103 union all
select 2105110 ,4 ,0104 union all
select 2105110 ,5 ,0108 union all
select 2105110 ,6 ,0105 union all
select 2101110 ,261 ,0101 union all
select 2101110 ,262 ,0102 union all
select 2101110 ,263 ,0103 union all
select 2101110 ,264 ,0104 union all
select 2101110 ,265 ,0106 union 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.
Post #1391707
Posted Sunday, December 02, 2012 4:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1391708
Posted Sunday, December 02, 2012 4:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1391709
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse