Simple query!

i have a table called measurements which has a column pat_id and visitdate.

The pat_id column identifies the patient, and the visitdate is the date of the patients visit.  A patient can have many visits.

How do i find the first visit for every patient?
soozhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That would be a simple GROUP BY on artist, and MIN date.
SELECT pat_id , Min(visitdate) as earliest_visit_date
FROM measurements 
GROUP BY pat_id

Open in new window

If you'd like some more reading on GROUP BY I have an image and code-heavy tutorial out there called SQL Server GROUP BY Solutions
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Jim, just one correction. Should be MIN function and not MAX since he wants the first visit and not the last one.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes, copy-paste error on my part, corrected.  Thanks.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

HainKurtSr. System AnalystCommented:
Here it is:

SELECT * from (
SELECT pat_id , visitdate, row_number() over (partition by pat_id order by visitdate desc) rn
FROM measurements 
)x where rn=1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
soozhAuthor Commented:
What i need is the whole row.

When i think about i could have easily done the Group by.  Its getting the row that i dont know how todo.

However each row has an id column so i could use the Group by to identify the rows and then use a second select to get the rows.
0
HainKurtSr. System AnalystCommented:
or this

SELECT * from (
  SELECT m.*, row_number() over (partition by pat_id order by visitdate) rn
  FROM measurements m
) x where rn=1

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>What i need is the whole row.
It would help if you could state all requirements in the original question, so experts don't spin their wheels giving a solution that doesn't meet all requirements.

The row number / partition / order by solution should work.

Also, give this a whirl, which determines the min date for each pat_id in a subquery, then joins on the whole table based on pat_id and visitdate to minimum visitdate.
SELECT m.*
FROM measurements m
   JOIN (SELECT pat_id , Min(visitdate) as earliest_visit_date
         FROM measurements 
         GROUP BY pat_id) m_min_dt ON m.pat_id = m_min_dt.pat_id AND m.visitdate = m_min_dt.earliest_visit_date

Open in new window

0
HainKurtSr. System AnalystCommented:
Row_Number()

http://msdn.microsoft.com/en-us/library/ms186734.aspx

group by/min/max join with original table does not look a good idea :)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>group by/min/max join with original table does not look a good idea :)
Please explain why.  Works on my end.
0
soozhAuthor Commented:
Sorry Jim.  i wrote "How do i find the first visit for every patient? "  I should have written how do i get the row.  However what i did not write was i wanted the visit date.  I guess i was not 100% clear.

So i used HainKurst solution in a cte and did a join on it....

Thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Sounds like a plan.  Thanks for the split, good luck with your project, and if the article helps you please click the big green 'Was this article helpful?' at the end.  -Jim
0
soozhAuthor Commented:
your articule is pretty good esp. the more complicated functions such as over, partition, rank.

thanks
0
HainKurtSr. System AnalystCommented:
>group by/min/max join with original table does not look a good idea :)
Please explain why.  Works on my end.

of course it works... just it is old way of doing this before those nice functions

Ranking Functions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189798.aspx

and I guess it is much faster & light on resources on server... also, the solution may give multiple records if there are records with same min/max value... grouping whole table and joining again with table does not seems best option especially when there are new ways of writing such queries...
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks.  I have an update in the works with a lot more scenarios, but I'm being bombarded by certain individuals for me to write an SSIS article or three, as there are very few here.  I'll send you a message once I've published the update.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.