Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

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.
0
bfuchs
Asked:
bfuchs
  • 3
  • 2
  • 2
1 Solution
 
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
 
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
Rey Obrero (Capricorn1)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
 
bfuchsAuthor Commented:
Excellent, Thank You!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now