-1

I'm trying to create a SQL QUERY where it shows the latest or no "DateOfSpecimenCollection" of a record in a table. Below i have two table called "Swab" and "ContactTracingHeader".

SELECT  CTH.AutoID, CTH.Firstname, CTH.Lastname, SW.DateOfSpecimenCollection 
FROM ContactTracingHeader AS CTH LEFT JOIN Swab AS SW ON CTH.AutoID = SW.PatientNo
WHERE SW.DateOfSpecimenCollection = (SELECT FIRST(DateOfSpecimenCollection) 
FROM Swab ORDER BY DateOfSpecimenCollection ASC);

What I'm trying to output is when a user in "Swab" table inputs multiple and the same "PatientNo." it will only show one record of "PatientNo" with the latest "DateOfSpecimenCollection" And its "Firstname" and "Lastname", And also output record of some Patient in "ContactTracingHeader" Table that doesn't have "DateOfSpecimenCollection" to "Swab" table, this is also the reason why i used the LEFT JOIN. I apologize as I'm beginner in SQL MS ACCESS and also for bad explanation because of my bad english and duplicate question. Thank you in advance!

This is the visual Representation of the SQL:

Table ContactTracingHeader:

|AutoID|Firstname|Lastname|
 
   1      John     Smith
   
   2      Felix    James

Table Swab:

|PatientNo|DateOfSpecimenCollection|
     
      1           07/21/2020
     
      1           07/22/2020
     
      1           08/01/2020

The query output that i'm trying to make:

|AutoID|Firstname|Lastname|DateOfSpecimenCollection|
  
    1     John      Smith       08/01/2020
   
    2     Felix     James        (Blank)

since "Felix James" don't have any record in Table swab, his "DateOfSpecimenCollection" is blank or nothing.

Aldrin Dino
  • 57
  • 7
  • update your question add a proper data sample and the expected result as tabular text – ScaisEdge Jul 21 '20 at 07:05
  • Does this answer your question? [How to get the latest record in each group using GROUP BY?](https://stackoverflow.com/questions/10999522/how-to-get-the-latest-record-in-each-group-using-group-by) – June7 Jul 21 '20 at 07:11
  • Thank you guys. The comments are really helpful it helped me finish my program. – Aldrin Dino Jul 21 '20 at 08:33

1 Answers1

1

You can substitute a subquery for any column in the resultset. Use that to get the latest. Your real query is about persons, so select from that in your main query.

SELECT  CTH.AutoID, 
        CTH.Firstname, 
        CTH.Lastname, 
        (
            SELECT MAX(DateOfSpecimenCollection)
            FROM Swab
            WHERE Swab.PatientNo = CTH.AutoID
        )  LatestDateOfSpecimenCollection
FROM ContactTracingHeader CTH

If you only want to include patients who have one or more swabs, add this on:

WHERE EXISTS (SELECT 0 FROM Swab WHERE PatientNo = CTH.AutoID)
John Wu
  • 50,556
  • 8
  • 44
  • 80
  • This actually solved my duplicated record problem and now I'm trying to output the latest "DateOfSpecimenCollection" however when i tried the `WHERE SW.DateOfSpecimenCollection = (SELECT FIRST(DateOfSpecimenCollection) FROM Swab ORDER BY DateOfSpecimenCollection ASC)` it always give me the error : You tried to execute a query that does not include the specified expression "DateOfSpecimenCollection" as part of an aggregate function – Aldrin Dino Jul 21 '20 at 07:50
  • Use `TOP 1` instead of `FIRST`, or use `MAX` – John Wu Jul 21 '20 at 07:53