Pharmica
asked on
Microsoft SQL query help- Selecting Max date
Greeings Experts,
I am having a problem completing a Microsoft SQL query. I have posted another question but still can not get this working. Here is problem. I have a table that looks like this
Table1
Studyinv_ID Visitdate Visit_Type_ID VisitName
1698 12/7/2006 26 Closeout
1699 10/20/2005 23 PSSV/Initiation
1699 10/20/2005 25 Monitoring
1700 10/31/2005 25 Monitoring
What I am trying to do is group by Studyinv_ID and show the max date and its corresponding VisitName. The problem I keep having is when there are 2 identical dates (ie rows 2,3). No matter what I try, I get 2 results in the output. For example, I want the output to look like this...
Studyinv_ID Visitdate Visit_Type_ID VisitName
1698 12/7/2006 26 Closeout
1699 10/20/2005 25 Monitoring
1700 10/31/2005 25 Monitoring
The key (i think) is to somehow incorporate the Visit_type_ID as a max but I cannot figure it out. I have attached the table data if you want to see it for reference. Thanks.
table.xls
I am having a problem completing a Microsoft SQL query. I have posted another question but still can not get this working. Here is problem. I have a table that looks like this
Table1
Studyinv_ID Visitdate Visit_Type_ID VisitName
1698 12/7/2006 26 Closeout
1699 10/20/2005 23 PSSV/Initiation
1699 10/20/2005 25 Monitoring
1700 10/31/2005 25 Monitoring
What I am trying to do is group by Studyinv_ID and show the max date and its corresponding VisitName. The problem I keep having is when there are 2 identical dates (ie rows 2,3). No matter what I try, I get 2 results in the output. For example, I want the output to look like this...
Studyinv_ID Visitdate Visit_Type_ID VisitName
1698 12/7/2006 26 Closeout
1699 10/20/2005 25 Monitoring
1700 10/31/2005 25 Monitoring
The key (i think) is to somehow incorporate the Visit_type_ID as a max but I cannot figure it out. I have attached the table data if you want to see it for reference. Thanks.
table.xls
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER