Link to home
Create AccountLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

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.
Avatar of Flyster
Flyster
Flag of United States of America image

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
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
Avatar of bfuchs

ASKER

@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
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

Avatar of bfuchs

ASKER

@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
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of bfuchs

ASKER

Excellent, Thank You!