CODEWITHSUNDEEP
sqlms-access
Aldrin Dino
Aldrin Dino

Reputation: 57

How to show latest date in sql with a condition

First Here's my table:

Table: Swab

| AutoID | Firstname | DateOfSpecimenCollection | isActive |  
    1       Aldrin           01/10/1990             TRUE                                                                                                                                    
    2       Aldrin           05/07/1996             TRUE
    3       Aldrin           09/02/2005             TRUE
    4       Aldrin           10/02/2012             FALSE
    5       Chris             (BLANK)               TRUE
    6       Chris            11/11/1999             FALSE
    7       James            12/12/2002             TRUE
    8       James            01/12/2006             TRUE
    9       James            05/05/2014             TRUE

OUTPUT QUERY that i want

| AutoID | Firstname | DateOfSpecimenCollection |
    3       Aldrin          09/02/2005 
    5       Chris             (BLANK)
    9       James           05/05/2014

Here, i want to create a query that will select a record of a person/user that has the latest DateOfSpecimenCollection. If the latest date with isActive set to TRUE, then it will output its own record. However , If the latest date with isActive set to FALSE, then it will output the record with the another latest date with isActive set to true(As shown from the example table above). If the record has no Date but has isActive condition set to TRUE it will still output its own record on the table(Note that if there's another record of Chris with a Date and TRUE Condition, that record will be outputed instead).

This is what i have done so far:

SELECT AutoID, Firstname, DateOfSpecimenCollection
FROM Swab
WHERE AutoID IN 

(SELECT TOP 1 AutoID FROM Swab AS Swab2 WHERE Swab2.PatientNo=Swab.PatientNo AND Swab.isActive = TRUE

ORDER BY Dupe.DateOfSpecimenCollection DESC);

It shows the correct information as above, However for some reason it doesn't select another record of a same person/user with the latest date and TRUE Condition. Instead it only sees the person with the latest date or the TOP 1 and only return the record if the isActive is set to true which is not what i want.

The Query that i sent above look like this when it runs:

| AutoID | Firstname | DateOfSpecimenCollection |
    9        James           05/05/2014

I've also tried using the IF() Statement next to the SELECT, but i failed, it duplicates the record and also get the record with the previous dates. sorry for long and confusing explanation and also for duplicate question. Thanks in advance!

Upvotes: 0

Views: 587

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Use aggregation with group by

SELECT max(autoid) as autoid,Firstname,max(DateOfSpecimenCollection)
FROM Swab
where isActive = TRUE
group by Firstname

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Using a correlated subquery to identify the matching records for each person is one option here:

SELECT AutoID, Firstname, DateOfSpecimenCollection
FROM Swab s1
WHERE DateOfSpecimenCollection = (SELECT MAX(s2.DateOfSpecimenCollection)
                                  FROM Swab s2
                                  WHERE s2.Firstname = s1.Firstname AND
                                        s2.isActive = 'TRUE');

Upvotes: 1

Related Questions