CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
HerrimanCoder
HerrimanCoder

Reputation: 7218

SQL Server - How to filter rows based on matching rows?

I have a complex query that feeds into a simple temp table named #tempTBRB.

select * from #tempTBRB ORDER BY AccountID yields this result set:

enter image description here

In all cases, when there is only 1 row for a given AccountID, the row should remain, no problem. But whenever there are 2 rows (there will never be more than 2), I want to keep the row with SDIStatus of 1, and filter out SDIStatus of 2.

Obviously if I used a simple where clause like "WHERE SDIStatus = 1", that wouldn't work, because it would filter out a lot of valid rows in which there is only 1 row for an AccountID, and the SDIStatus is 2.

Another way of saying it is that I want to filter out all rows with an SDIStatus of 2 ONLY WHEN there is another row for the same AccountID. And when there are 2 rows for the same AccountID, there will always be exactly 1 row with SDIStatus of 1 and 1 row with SDIStatus of 2.

I am using SQL Server 2012. How is it done?

Upvotes: 2

Views: 2395

Answers (3)

kseely
kseely

Reputation: 44

SELECT
  AccountID
 ,MIN(SDIStatus) AS MinSDIStatus
INTO #MinTable
FROM #tempTBRB
GROUP BY AccountID

SELECT *
FROM #tempTBRB T
JOIN #MinTable M ON
     T.AccountID = M.AccountID
     AND T.SDIStatus = M.MinSDIStatus

DROP TABLE #MinTable

Upvotes: 2

Satheesh Variath
Satheesh Variath

Reputation: 680

I guess you need a similar code, make the necessary changes according to your table structure 

declare @tab table (ID INT IDENTITY (1,1),AccountID int,SDISTATUS int)
insert into @tab values(4137728,1),(4137728,2),(41377,1),(41328,2)

select * from 
(select *, row_number()OVER(Partition by AccountID Order by SDISTATUS ) RN from @tab) T
where t.RN=1

Or

WITH CTE AS 
(select *, row_number()OVER(Partition by AccountID Order by SDISTATUS ) RN from @tab) 
select * from CTE where t.RN=1

Upvotes: 0

Facio Ratio
Facio Ratio

Reputation: 3393

Here is a little test that worked for me. If you just add the extra columns in your SELECT statements, all should be well:

CREATE TABLE #Temp ( ID int, AccountID int, Balance money, SDIStatus int )

INSERT INTO #Temp ( ID, AccountID, Balance, SDIStatus ) VALUES ( 1, 4100923, -31.41, 2 )
INSERT INTO #Temp ( ID, AccountID, Balance, SDIStatus ) VALUES ( 2, 4132170, 0, 2 )
INSERT INTO #Temp ( ID, AccountID, Balance, SDIStatus ) VALUES ( 3, 4137728, 193.10, 1 )
INSERT INTO #Temp ( ID, AccountID, Balance, SDIStatus ) VALUES ( 4, 4137728, 0, 2 )

SELECT ID, AccountID, Balance, SDIStatus
FROM
(
    SELECT ID, AccountID, Balance, SDIStatus,
        row_number() over (partition by AccountID order by SDIStatus desc) as rn
    FROM #Temp
) x
WHERE x.rn = 1

DROP TABLE #Temp

Yields the following:

ID  AccountID  Balance  SDIStatus
1   4100923    -31.41   2
2   4132170    0.00     2
4   4137728    0.00     2

Upvotes: 1

Related Questions