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 Sunday, December 2, 2012 4:11 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1391710
Posted Sunday, December 2, 2012 4:18 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1391712
Posted Sunday, December 2, 2012 8:17 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 20,734, Visits: 32,505
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 ,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;

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





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 #1391727
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse