Link to home
Create AccountLog in
Avatar of Pharmica
PharmicaFlag for Afghanistan

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
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Pharmica

ASKER

Impressive.  I have struggled with this for 2 days.   Your solution worked perfectly.