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 !

Search "first in group" - row_number is the common approach to such issues. TBH it seems like you could just filter on `LastDt is null` but your image cannot speak and it is not clear why you have highlighted some groups and not others. — SMor, Jan 27 '22 at 17:30
i dont know how to post a DataTable ..... and i wanted to explicitly point out some data @SMor yeah i know ive put only the last information , sorry i did this real quick, but at the end, with thoses records, i want to be able to do a select * and if i got 2 FolderName with the same FullDt, keep the records where LastDt is null, — Thapipo, Jan 27 '22 at 17:42
You have multiple rows for A-100016 (and others) - why do you include both? And I'm not going to visually examine all those rows for problems which is why it is HIGHLY recommended to post information that can be directly consumed. — SMor, Jan 27 '22 at 21:11
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jan 28 '22 at 13:45

score 0 · Answer 1 · edited Jan 28 '22 at 13:45

0

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

edited Jan 28 '22 at 13:45

Charlieface

answered Jan 27 '22 at 18:02

GSM

This will exclude single rows. OP wants to exclude only "doubles". – Serg Jan 27 '22 at 18:08
He wants to chose from the records where FolderName and FullDt is same (hence the GROUP BY), only the records which have LastDt as NULL( criterion in HAVING clause), so the records which are having LastDt not null are excluded – GSM Jan 27 '22 at 18:11
**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 – Thapipo Jan 27 '22 at 18:39
@Thapipo, did you try my solution? is it not giving your expected result? Or when you say you only want to keep the records where LastDt is null, meaning you want to delete the other record with same FolderName and FullDt? – GSM Jan 27 '22 at 18:41
@GSM your solution is not working, ive updated the picture for you to visualize what i need – Thapipo Jan 27 '22 at 20:46

1 Answers1