Sql question

Hi Experts,

I have a table containing:

ID
PatientID
DateStarted
Subject

How do I get the ID of the latest day per patient, per subject?
I would prefer to have this in one query/view.
LVL 4
bfuchsAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
change "What Subject" with the subject you want to exclude


SELECT A.*
 FROM TableX AS A
 INNER JOIN
 (SELECT PatientID, Max([DateStarted]) AS MaxOfDate, Subject
      FROM TableX  
      Where Subject <> "What Subject"
      GROUP BY PatientID, Subject)  AS B
      ON A.[DateStarted]=B.MaxOfDate AND A.PatientID=B.PatientID AND A.Subject=B.Subject
0
 
FlysterCommented:
Try this:

SELECT tblYourTable.[PatientID], Last(tblYourTable.[DateStarted]) AS LastOfDateStarted, Last(tblYourTable.[Subject]) AS LastOfSubject
FROM tblYourTable
GROUP BY tblYourTable.[PatientID];

Open in new window


Flyster
0
 
Rey Obrero (Capricorn1)Commented:
try this query


SELECT A.*
 FROM TableX AS A
 INNER JOIN
 (SELECT PatientID, Max([DateStarted]) AS MaxOfDate, Subject
      FROM TableX  GROUP BY PatientID, Subject)  AS B
      ON A.[DateStarted]=B.MaxOfDate AND A.PatientID=B.PatientID AND A.Subject=B.Subject
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bfuchsAuthor Commented:
@Ray,
It works great,
Just one question, if I want to exclude a subject where do I put it?

@Flyster,
I cant see how your example would work in my case, I need the ID of the record containing the highest date of patient per subject.

Thanks,
Ben
0
 
FlysterCommented:
Sorry, missed that part. How about this:
SELECT Last(tblYourTable.ID) AS LastOfID, tblYourTable.PatientID, Last(tblYourTable.DateStarted) AS LastOfDateStarted, Last(tblYourTable.Subject) AS LastOfSubject
FROM tblYourTable
GROUP BY tblYourTable.PatientID

Open in new window


To exclude a particular subject, see the last line:
SELECT Last(tblYourTable.ID) AS LastOfID, tblYourTable.PatientID, Last(tblYourTable.DateStarted) AS LastOfDateStarted, Last(tblYourTable.Subject) AS LastOfSubject
FROM tblYourTable
GROUP BY tblYourTable.PatientID
HAVING (((Last(tblYourTable.Subject))<>"Enter_Your_Subject_Here"));

Open in new window

0
 
bfuchsAuthor Commented:
@Flyster,

Last(tblYourTable.ID) AS LastOfID..Group by PatientID would give me the highest ID per patient, not necessary the ID that belongs to the records containing the highest date.

Re excluding one subject, can you tell me how to modify Ray's suggestion to include this portion?

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Excellent, Thank You!
0
All Courses

From novice to tech pro — start learning today.