;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 = 1Results:rn office_id Binder_id Binder_no1 2105110 10 1011 2105110 12 1021 2105110 14 1031 2105110 16 1041 2105110 17 1051 2105110 20 106
CREATE TABLE [REV_BINDERS]([OFFICE_ID] [int] NOT NULL,[BINDER_ID] [int] NOT NULL,[BINDER_NO] [varchar](7) NULL,);goINSERT INTO [REV_BINDERS] ([OFFICE_ID],[BINDER_ID],[BINDER_NO])select 2105110,10,0101 union allselect 2105110,12,0102 union allselect 2105110,13,0102 union allselect 2105110,14,0103 union allselect 2105110,15,0103 union allselect 2105110,16,0104 union allselect 2105110,17,0105 union allselect 2105110,18,0105 union allselect 2105110,19,0105 union allselect 2105110,20,0106;gowith emp as (select OFFICE_ID, BINDER_ID, BINDER_NO, count(*) over (PARTITION BY OFFICE_ID, BINDER_NO) as BinderCntfrom REV_BINDERS )select * from emp where BinderCnt = 1;goDROP TABLE [REV_BINDERS];go
CREATE TABLE [REV_BINDERS]([OFFICE_ID] [int] NOT NULL,[BINDER_ID] [int] NOT NULL,[BINDER_NO] [varchar](7) NULL,);goINSERT INTO [REV_BINDERS] ([OFFICE_ID],[BINDER_ID],[BINDER_NO])select 2105110 , 1 ,0101 union allselect 2105110 ,2 ,0102 union allselect 2105110 ,3 ,0103 union allselect 2105110 ,4 ,0104 union allselect 2105110 ,5 ,0105 union allselect 2105110 ,6 ,0108 union allselect 2101110 ,261 ,0101 union allselect 2101110 ,262 ,0102 union allselect 2101110 ,263 ,0103 union allselect 2101110 ,264 ,0104 union allselect 2101110 ,265 ,0105 union allselect 2101110 ,266 ,0106
SELECT rb.OFFICE_ID, MAX(rb.BINDER_ID), MAX(rb.BINDER_NO)FROM dbo.REV_BINDERS AS rbGROUP BY rb.OFFICE_IDORDER BY rb.OFFICE_ID;
+-----------+------------------+------------------+| OFFICE_ID | (No column name) | (No column name) |+-----------+------------------+------------------+| 2101110 | 266 | 106 || 2105110 | 6 | 108 |+-----------+------------------+------------------+
CREATE TABLE [REV_BINDERS]([OFFICE_ID] [int] NOT NULL,[BINDER_ID] [int] NOT NULL,[BINDER_NO] [varchar](7) NULL,);goINSERT INTO [REV_BINDERS] ([OFFICE_ID],[BINDER_ID],[BINDER_NO])select 2105110 , 1 ,0101 union allselect 2105110 ,2 ,0102 union allselect 2105110 ,3 ,0103 union allselect 2105110 ,4 ,0104 union allselect 2105110 ,5 ,0108 union allselect 2105110 ,6 ,0105 union allselect 2101110 ,261 ,0101 union allselect 2101110 ,262 ,0102 union allselect 2101110 ,263 ,0103 union allselect 2101110 ,264 ,0104 union allselect 2101110 ,265 ,0106 union allselect 2101110 ,266 ,0105
SELECT t.OFFICE_ID, t.BINDER_NO, t.BINDER_IDFROM ( 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 tWHERE t.group_count = 1;
+-----------+-----------+-----------+| OFFICE_ID | BINDER_NO | BINDER_ID |+-----------+-----------+-----------+| 2101110 | 106 | 265 || 2105110 | 108 | 5 |+-----------+-----------+-----------+
SELECT rb.OFFICE_ID, rb.BINDER_NO, rb.BINDER_IDFROM dbo.REV_BINDERS AS rbWHERE EXISTS ( SELECT * FROM dbo.REV_BINDERS AS rb2 WHERE rb2.BINDER_NO = rb.BINDER_NO GROUP BY rb2.BINDER_NO HAVING COUNT_BIG(*) = 1 );