Extract one record per unique ID

[foxtrot123]

I have a table like so:

PatientID  VisitDate  Complaint TestPos
1          4/5/2003   Coughing  1
1          1/2/2007   Sneezing  
1          5/1/2008   Unknown   1
2          2/1/1988   Unknown
2          4/2/1988   Unknown   1

I'd like to extract just one TestPos record (TestPos = 1) per Patient ID. And I always want to select the record with the earliest date. So the result would be:

PatientID  VisitDate  Complaint TestPos
1          4/5/2003   Coughing  1
2          4/2/1988   Unknown   1

I can do this but it requires 3 queries: a SELECT query where TestPos = 1, a GROUP BY PatientID query and MIN(VisitDate) to get the desired record per patient, and then another query that links the two so I can get the additional variables (e.g., Complaint).

Is there a way to do this without using 3 queries? Seems inefficient.

 

[foxtrot123]

Got it! Use a total query with Group By for PatientID, Min for VisitDate, First for any other variables needed, like Complaint, and Where for TestPos.

SELECT TestTable.PatientID, Min(TestTable.VisitDate) AS MinOfVisitDate, First(TestTable.Complaint) AS FirstOfComplaint
FROM TestTable
WHERE (((TestTable.TestPos)="1"))
GROUP BY TestTable.PatientID;

 

[namliam]

First is not quite THAT reliable, If you want to be sure you get your first date... the only way to be sure is to use your original query(s)

 

[CJ_London]

another option is

 SELECT *
 FROM TestTable
 WHERE visitDate=(SELECT min(visitDate) from TestTable as T where PatientID=TestTable.PatientID)
 ORDER BY PatientID