Filter out entries with negative numbers and duplicates (1 Viewer)

[gojets1721]

Hi so I attached an example table. Essentially, I want to create a query that looks at the DocumentAge field and firstly, filters out all entries where the Document Age is negative. After that, it also filters out duplicate entries and only leaves the entry with the lowest DocumentAge.

So based on my example, ID 7, 2, 1 would pull into the query and 11, 3, 9, 8, 10 would not.

I think I know how to use criteria to individually filter out negative and minimums but I was just looking for guidance on how to do both in the most effective and cleanest manner (one query, multiple, etc.)

 

[jdraw]

Try

SELECT Min(tblDocuments.DocumentAge) AS MinOfDocumentAge
, tblDocuments.DocumentID
FROM tblDocuments where documentAge >=0
GROUP BY tblDocuments.DocumentID;

 

[arnelgp]

SELECT tblDocuments.ID, tblDocuments.DocumentID, tblDocuments.CustomerFirstName, tblDocuments.CustomerLastName, tblDocuments.DocumentAge
FROM tblDocuments
WHERE DocumentAge = (SELECT MIN(DocumentAge) FROM tblDocuments AS T1 WHERE T1.DocumentID = tblDocuments.DocumentID AND T1.DocumentAge > -1)