SELECT to only see Offsetting Transactions in table? I have a temp table like this

I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos.
So, I only want to see

CCC 333333333  Ambition Trust  Purchase,
CCC 333333333  Ambition Trust  Sell

This is the SQL giving me the same Portcode, Cusip combo from a table.
SELECT PortfolioName, CUSIP, COUNT(*) AS CNT
INTO #tmpCusip
FROM #tmpTrades 
GROUP BY PortfolioName, MCUSIP
HAVING COUNT(*) &gt; 1

Does anyone have any ideas?
I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos.


There is no Buy in your table. You only have Sell and Purchase. 

Below is a good starting point for you to get (Purchase,Sell) combo : 

;with temptrades 
as
(select *, row_number() over (partition  by PortfolioName, [transaction]
order by PortfolioName, [transaction]) as rowNumber
    from tmpTrades
)
select  portcode,cusip, PortfolioName,  [transaction]   
 from temptrades    
where  rowNumber = 1
order by portcode




SQLFiddle here.