Complex SQL query (SQL Server)

Question

i need a little help.

Let say ive this recordset below, im using Common Table Expressions, i'm at my last step which is :

If for a FolderName, i have the same FullDt value, i want to be able to select the row that the LastDate IS NULL,

so at the end, on my last select, i'll end up with unique FolderName and FullDt.

So the logic is :

select * (everything)

where ( If i got 2 records or more with the same FolderName and same FullDt, take the record where LastDt is Null.)

It's sound pretty simple and easy, but i can't figure it out on my sql query.

**I've updated the picture to be more relevant of what i want to do, the records with red marked lines, i dont want them anymore , why ?

cause there's 2 records with the same FolderName and FullDt, so i just want to keel the records where LastDt is Null

Thanks you for your time and your help !

Answer 1

You can try grouping the records by FolderName and FullDt and specify your criteria in HAVING clause

Something like below

SELECT COUNT(1), FolderName, AgentID, FullDt  
FROM MyTable         
GROUP BY FolderName, FullDt        
HAVING COUNT(1) > 1 AND LastDt IS NULL