I really did not understand the query why you have Count(T1.CID) = 1 then T.CID in the order by clause.. I have rewritten the code to below by changing the order by clause

..tested with few scenarios and seems to work ..Still trying to figure out what was the intention of having count(T.Cid) = 1.. Am I missing something here ?

Trying to work on the Date logic to fit into this

; WITH RankedListed (CNo, CId, [Rank]) AS

(

SELECT T.CNO

,T.CId

,ROW_NUMBER()

OVER ( PARTITION BY T.CNO

ORDER BY COUNT(T1.CId) DESC , T.CID

DESC) AS 'Rank'

FROM #temp T

LEFT JOIN #temp1 T1

ON T.CNO = T1.CNo

AND T.CId = T1.CId

GROUP BY T.CNO

,T.CId

--ORDER BY T.CNO

--,T.CId

)

SELECT RL.CNo , RL.CId

FROM RankedListed RL

WHERE RL.[Rank] = 1